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

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

On Thursday, August 21, 2014 6:27:13 PM UTC-7, GS wrote:
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


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

On Thursday, August 21, 2014 6:27:13 PM UTC-7, GS wrote:
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


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

On Thursday, August 21, 2014 7:34:01 PM UTC-7, GS wrote:
On Thursday, August 21, 2014 6:27:13 PM UTC-7, GS wrote:

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





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


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/y1sx72rxgs...rop%20Box.xlsm

Howard

Garry,

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

https://www.dropbox.com/s/ep41xcpsw5...20Box%20X.xlsm

Howard