Modify Claus code "Sub Array_Var_Column_Sort()"

[L. Howard]

This seems to work but I don't trust my judgment on evaluating the outcome.

Trying to apply the row count of each variable length column to the specific column to sort.

Lrow1, 2, 3 show correct row numbers in varRow = Array(LRow1, LRow2, LRow3)

Using A1, C1 & E1 pulled down to various row lengths for testing.

I have gotten a 6 row gap in the sort in column A once in awhile.

Thanks.
Howard


Sub Array_Var_Column_Sort()

Dim LRow1 As Long, LRow2 As Long, LRow3 As Long
Dim LRowX As Long, LCol As Long
Dim i As Long, ii As Long
Dim varKey As Variant
Dim varRow As Variant

Application.ScreenUpdating = False

With Sheets("sheet5")
'Last rows
LRow1 = .Cells(Rows.Count, 1).End(xlUp).Row
LRow2 = .Cells(Rows.Count, 3).End(xlUp).Row
LRow3 = .Cells(Rows.Count, 5).End(xlUp).Row

'Last column
'LCol = .Cells(1, Columns.Count).End(xlLeft).Column
LCol = Cells.Find(What:="*", After:=[a1], _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column

'Sortkeys
varKey = Array("A1", "C1", "E1")
varRow = Array(LRow1, LRow2, LRow3)

.Sort.SortFields.Clear
For i = LBound(varKey) To UBound(varKey)
For ii = LBound(varRow) To UBound(varRow)

.Range(.Cells(1, 1), .Cells(varRow(ii), LCol)).Sort _
Key1:=.Range(varKey(i)), order1:=xlAscending, Header:=xlNo 'Header:=xlYes

Next
Next
End With
Application.ScreenUpdating = True
End Sub

 

[GS]

LCol = Cells.Find(What:="*", After:=[a1], _

SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column

Curious why you used this instead of...

lLastCol = Cells(1, Columns.Count).End(xlToLeft).Column

...as you have in the line above (which contains the wrong direction
constant)! This makes for better self-documenting, consistency, and
code brevity!

Otherwise, I don't see in your code why you occasionally get gaps in
colA. Perhaps a runtime issue? Hard to say without knowing the actual
data you're trying to sort. What's the runtime ratio for it to occur?
You can send me the file if you want to see if it happens for me...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[L. Howard]

LCol = Cells.Find(What:="*", After:=[a1], _

SearchOrder:=xlByColumns, _

SearchDirection:=xlPrevious).Column

Curious why you used this instead of...



lLastCol = Cells(1, Columns.Count).End(xlToLeft).Column



..as you have in the line above (which contains the wrong direction

constant)! This makes for better self-documenting, consistency, and

code brevity!



Otherwise, I don't see in your code why you occasionally get gaps in

colA. Perhaps a runtime issue? Hard to say without knowing the actual

data you're trying to sort. What's the runtime ratio for it to occur?

You can send me the file if you want to see if it happens for me...

Hi Garry,

lLastCol = Cells(1, Columns.Count).End(xlToLeft).Column

This returns 16384, did not seem to right with only three columns A, C & D on my test sheet. I wondered if Claus was setting the sheet to do any number of columns, but don't know.

As far as the sheet I'm testing on it is a blank new sheet and in column A "a1" and pull down 20-30 rows, same with C and D columns. I then mix the data and run the code. So the sheet is real plain jane.

The gap in col A is not consistent, strange and it could have been something I did and was not aware of. Sorta the reason I posted here to get a critical review of the mods I made.

I have no project for this, just occurred to me to try it since not all columns may be the exact same number of rows.

Claus' code took the column length from a single column and applied it to all.

So low priority for sure.

Howard

 

[GS]

LCol = Cells.Find(What:="*", After:=[a1], _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column

Curious why you used this instead of...



lLastCol = Cells(1, Columns.Count).End(xlToLeft).Column



..as you have in the line above (which contains the wrong direction

constant)! This makes for better self-documenting, consistency, and

code brevity!



Otherwise, I don't see in your code why you occasionally get gaps in

colA. Perhaps a runtime issue? Hard to say without knowing the
actual

data you're trying to sort. What's the runtime ratio for it to
occur?

You can send me the file if you want to see if it happens for me...

Hi Garry,

lLastCol = Cells(1, Columns.Count).End(xlToLeft).Column

This returns 16384, did not seem to right with only three columns A,
C & D on my test sheet. I wondered if Claus was setting the sheet to
do any number of columns, but don't know.

As far as the sheet I'm testing on it is a blank new sheet and in
column A "a1" and pull down 20-30 rows, same with C and D columns. I
then mix the data and run the code. So the sheet is real plain jane.

The gap in col A is not consistent, strange and it could have been
something I did and was not aware of. Sorta the reason I posted here
to get a critical review of the mods I made.

I have no project for this, just occurred to me to try it since not
all columns may be the exact same number of rows.

Claus' code took the column length from a single column and applied
it to all.

So low priority for sure.

Howard

Ok! The file you're playing with IS a project and so if you send it to
me I'll play too!<g>

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[L. Howard]

LCol = Cells.Find(What:="*", After:=[a1], _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column



Curious why you used this instead of...



lLastCol = Cells(1, Columns.Count).End(xlToLeft).Column



..as you have in the line above (which contains the wrong direction

constant)! This makes for better self-documenting, consistency, and

code brevity!



Otherwise, I don't see in your code why you occasionally get gaps in

colA. Perhaps a runtime issue? Hard to say without knowing the
actual

data you're trying to sort. What's the runtime ratio for it to
occur?

You can send me the file if you want to see if it happens for me...

Hi Garry,

lLastCol = Cells(1, Columns.Count).End(xlToLeft).Column

This returns 16384, did not seem to right with only three columns A,

C & D on my test sheet. I wondered if Claus was setting the sheet to

do any number of columns, but don't know.

As far as the sheet I'm testing on it is a blank new sheet and in

column A "a1" and pull down 20-30 rows, same with C and D columns. I

then mix the data and run the code. So the sheet is real plain jane.

The gap in col A is not consistent, strange and it could have been

something I did and was not aware of. Sorta the reason I posted here

to get a critical review of the mods I made.

I have no project for this, just occurred to me to try it since not

all columns may be the exact same number of rows.

Claus' code took the column length from a single column and applied

it to all.

So low priority for sure.

Howard

Ok! The file you're playing with IS a project and so if you send it to

me I'll play too!<g>

And after more testing the gap is now consistent but is in column C.

Data drops about 8 rows and the text to the right also is moved down several rows.

This is now in the weird category!

https://www.dropbox.com/s/y1sx72rxgsk55tj/Book2 Drop Box.xlsm

Howard

 

[L. Howard]

Garry,

Try this one, I think I sent wrong link in last post

https://www.dropbox.com/s/ep41xcpsw5uiiwa/Book2 Drop Box X.xlsm

Howard

 

[GS]

Garry,

Try this one, I think I sent wrong link in last post

https://www.dropbox.com/s/ep41xcpsw5uiiwa/Book2 Drop Box X.xlsm

Howard

Using .Find includes your notes in colG (returns 7).

Using .Cells(1, Columns.Count).End(xlToLeft).Column does not include
your notes (returns 5).

So 'as is' your notes are included in the sort so I commented out the
..Find code.

Your loops sort each column by each row count. What is the intent here?

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[L. Howard]

Using .Find includes your notes in colG (returns 7).



Using .Cells(1, Columns.Count).End(xlToLeft).Column does not include

your notes (returns 5).



So 'as is' your notes are included in the sort so I commented out the

.Find code.



Your loops sort each column by each row count. What is the intent here?

Okay, I did notice 7 in the msgbox pop up, but did not make the connection.

So now I believe the problem with the gap in Col A I mentioned earlier was due to the same problem, where I most likely had some data out side column 5 and below the end rows of C & D. Col A was 30 rows deep and that was theissue for sure.

Mostly what I was trying to do here was practice on arrays. After seeing the columns to sort in an array and I noticed that if the columns were different lengths, all columns would only be sorted down to the last columns length, as it was the value for Lrow for each column.

So the mission was to put each columns Lrow in an array and apply it to theproper column from the column array.

Looks like I got it mostly accomplished but was sidetracked by side show antics of my own doing.

So it still leaves me with the question, suppose there are three columns tosort A C & D. And each a different number of rows. And to the right are many other columns.

Now, it just dawns on me that you don't need the last column line of code, you can use i as the column in:

..Range(.Cells(1, 1), .Cells(varRow(ii), i)).Sort _
Key1:=.Range(varKey(i)), order1:=xlAscending, Header:=xlNo

Hmmm, haven't tried it but will do so now.

Back in a bit.

Howard

 

[L. Howard]

Now, it just dawns on me that you don't need the last column line of code, you can use i as the column in:



.Range(.Cells(1, 1), .Cells(varRow(ii), i)).Sort _

Key1:=.Range(varKey(i)), order1:=xlAscending, Header:=xlNo



Hmmm, haven't tried it but will do so now.



Back in a bit.

Well, that did not work. Errors out, I tried i + 1 also, but no go. (with both i's.)

..Range(.Cells(1, 1), .Cells(varRow(ii), i)).Sort _
Key1:=.Range(varKey(i)), order1:=xlAscending, Header:=xlNo

Howard

 

[Claus Busch]

Hi Howard,

Am Thu, 21 Aug 2014 22:43:37 -0700 (PDT) schrieb L. Howard:

.Range(.Cells(1, 1), .Cells(varRow(ii), i)).Sort _
Key1:=.Range(varKey(i)), order1:=xlAscending, Header:=xlNo

why are sorting with different rows?
Try:

Sub Array_Var_Column_Sort()

Dim LRow As Long
Dim LRowX As Long, LCol As Long
Dim i As Long, ii As Long
Dim varKey As Variant

Application.ScreenUpdating = False

With Sheets("sheet5")
LRow = .UsedRange.Rows.Count
'Last column
'LCol = .Cells(1, Columns.Count).End(xlLeft).Column
LCol = Cells.Find(What:="*", After:=[a1], _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column

'Sortkeys
varKey = Array("A1", "C1", "E1")

.Sort.SortFields.Clear
For i = LBound(varKey) To UBound(varKey)
.Range(.Cells(1, 1), .Cells(LRow, LCol)).Sort _
Key1:=.Range(varKey(i)), order1:=xlAscending, Header:=xlNo
'Header:=xlYes

Next
End With
Application.ScreenUpdating = True
End Sub


Regards
Claus B.

 

[L. Howard]

I tried this, which put i to 1 (one) in the code line, but still errors out.

For i = LBound(varKey) To UBound(varKey)
For ii = LBound(varRow) To UBound(varRow)
i = i + 1
.Range(.Cells(1, 1), .Cells(varRow(ii), i)).Sort _
Key1:=.Range(varKey(i)), order1:=xlAscending, Header:=xlNo 'Header:=xlYes

Next
Next

Howard

 

[GS]

So it still leaves me with the question, suppose there are three

columns to sort A C & D. And each a different number of rows. And
to the right are many other columns.

Why not simplify it...

Const sColsToSort$ = "A,C,D"
varKey = Split(sColsToSort, ",")

'''
For i = LBound(varKey) To UBound(varKey)
.Columns(varKey(i)).Sort Key1:=.Cells(1, varKey(i))...

...where there's no need to know row count because blank cells will be
below cells conatining data. So there's really 2 ways to go with
this...

Option Explicit

Const sColsToSort$ = "A,C,E"

Sub Array_Var_Column_Sort2()
Dim vCols, n&

Application.ScreenUpdating = False
vCols = Split(sColsToSort, ",")
With Sheets("sheet1")
' .Cells.SortFields.Clear'//late versions only
For n = LBound(vCols) To UBound(vCols)
.Columns(vCols(n)).Sort Key1:=.Cells(1, vCols(n)), _
order1:=xlAscending, Header:=xlNo 'Header:=xlYes
Next
End With
Application.ScreenUpdating = True
End Sub

Sub Array_Var_Column_Sort3()
Dim vCols, vCol

Application.ScreenUpdating = False
With Sheets("sheet1")
' .Cells.SortFields.Clear'//late versions only
For Each vCol In Split(sColsToSort, ",")
.Columns(vCol).Sort Key1:=.Cells(1, vCol), _
order1:=xlDescending, Header:=xlNo 'Header:=xlYes
Next
End With
Application.ScreenUpdating = True
End Sub

...where the 2nd sub uses a 'virtual' array. Not what you're working on
but thought you might appreciate the similarities.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[L. Howard]

Hi Howard,



Am Thu, 21 Aug 2014 22:43:37 -0700 (PDT) schrieb L. Howard:

.Range(.Cells(1, 1), .Cells(varRow(ii), i)).Sort _

Key1:=.Range(varKey(i)), order1:=xlAscending, Header:=xlNo

why are sorting with different rows?

Try:



Sub Array_Var_Column_Sort()



Dim LRow As Long

Dim LRowX As Long, LCol As Long

Dim i As Long, ii As Long

Dim varKey As Variant



Application.ScreenUpdating = False



With Sheets("sheet5")

LRow = .UsedRange.Rows.Count

'Last column

'LCol = .Cells(1, Columns.Count).End(xlLeft).Column

LCol = Cells.Find(What:="*", After:=[a1], _

SearchOrder:=xlByColumns, _

SearchDirection:=xlPrevious).Column



'Sortkeys

varKey = Array("A1", "C1", "E1")



.Sort.SortFields.Clear

For i = LBound(varKey) To UBound(varKey)

.Range(.Cells(1, 1), .Cells(LRow, LCol)).Sort _

Key1:=.Range(varKey(i)), order1:=xlAscending, Header:=xlNo

'Header:=xlYes



Next

End With

Application.ScreenUpdating = True

End Sub





Regards

Claus B.

Hi Claus,

I was trying to amend your code to sort columns A C & D if they were each a different number of rows long. Say 30 9 and 15 respectively.

Also, I was assuming many columns to the right of the three to sort which I don't want to sort.

So my exercise was to try to use an array of the column lengths to apply to the correct column from the column array.

Just a practice task trying to learn more about arrays.

Additional data to the right of the three columns causes some problems when using .Find and the Last column line of your uses the column number to the far right.

Here is my test sheet where the code sees the text to the right of the columns and responds to it, instead of just to the three columns.

https://www.dropbox.com/s/ep41xcpsw5uiiwa/Book2 Drop Box X.xlsm

Howard

 

[GS]

' .Cells.SortFields.Clear'//late versions only

Mis-typed in 2003! Above line should read...

' .Sort.SortFields.Clear'//late versions only

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Claus Busch]

Hi Howard,

Am Thu, 21 Aug 2014 22:53:16 -0700 (PDT) schrieb L. Howard:

I tried this, which put i to 1 (one) in the code line, but still errors out.

try:

Sub Array_Var_Column_Sort()
'/ Claus

Dim LRow As Range
Dim Lcol As Long
Dim i As Long
Dim varKey As Variant

Application.ScreenUpdating = False

With Sheets("sheet1")

'Last column
Lcol = .Cells(1, Columns.Count).End(xlToLeft).Column

'LCol is not needed because you sort each column in a seperate loop.
'If you look for last column you have to use xlToLeft and NOT xlUp

'Sortkeys
varKey = Array("A1", "C1", "E1")
.Sort.SortFields.Clear
For i = LBound(varKey) To UBound(varKey)
Set LRow = .Cells(Rows.Count, Asc(Left(varKey(i), 1)) -
64).End(xlUp)
.Range(varKey(i), LRow).Sort _
Key1:=.Range(varKey(i)), order1:=xlDescending, Header:=xlNo
'Header:=xlYes
Next
'
End With
Application.ScreenUpdating = True
End Sub


Regards
Claus B.

 

[L. Howard]

Hi Garry and Claus,

Thanks for the schooling, got some good info and sort codes to study.

You guys are great! Appreciate it.

Regards,
Howard

 

[Claus Busch]

Hi,

Am Fri, 22 Aug 2014 00:19:22 -0700 (PDT) schrieb L. Howard:

Thanks for the schooling, got some good info and sort codes to study.

always glad to help.

If you want to sort the whole table range first by key1 then by key2
then by key3....

try:

Sub SortTest()
Dim LRow As Long
Dim Lcol As Long
Dim i As Long
Dim varKey As Variant

Application.ScreenUpdating = False

varKey = Array("A1", "C1", "E1")

With Sheets("Sheet1")
Lcol = .Cells(1, Columns.Count).End(xlUp).Column
LRow = .UsedRange.Rows.Count

.Sort.SortFields.Clear

For i = LBound(varKey) To UBound(varKey)
.Sort.SortFields.Add Key:=.Range(varKey(i)) _
, SortOn:=xlSortOnValues, Order:=xlDescending
Next

With .Sort
.SetRange Range(Cells(1, 1), Cells(LRow, Lcol))
.Header = xlNo
.MatchCase = False
.Apply
End With
End With

Application.ScreenUpdating = True
End Sub


Regards
Claus B.

 

[GS]

Nice!
The newer versions are much more robust since Sort was added to
Worksheet...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[GS]

Nice!

The newer versions are much more robust since Sort was added to
Worksheet...

Bummer that Sort *still* doesn't work well with numeric sequencing!!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Claus Busch]

Hi Garry,

Am Fri, 22 Aug 2014 13:08:13 -0400 schrieb GS:

Bummer that Sort *still* doesn't work well with numeric sequencing!!

..Sort.SortFields.Add Key:=.Range(varKey(i)) _
, SortOn:=xlSortOnValues, Order:=xlDescending

delete SortOn:=xlSortOnValues
Then most of the times Sort works well
If not, please send me your example


Regards
Claus B.