Dynamic nested sorts using Excel VBA

Question

I've been to this MSDN page to learn about sorting with multiple sort fields. It basically says to number your keys and set them equal to the sort field.

I want to loop through an N sized integer array to sort a range by the values in the array. For example, if my sheet has 100 columns of data, I might want to sort based on columns 3,18 and 62; so N would be 3. The problem is I can't name the sort keys "key" & i as i loops from 1 to N.

What I have so far:

 With Worksheets("SalesRep").Sort
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    for i=1 to myArrayLength
       SortFields.Add Key:=Range(cells(1,colNumArray(i)).address,cells(lastRow,colNumArray(i)).address)
    next i
    .Apply
End With

What do you recommend?

@TimWilliams added. I used generic names for several variables, but I think it's clear. Basically, after looping through each value in my array, I then apply the sort field. — wizlog
– wizlog, Commented Aug 9, 2017 at 23:06
No error message, yet it doesn't sort. I looked for ways to determine applied sort fields using the immediate window but came up empty handed. — wizlog
– wizlog, Commented Aug 9, 2017 at 23:10

Tim Williams · Accepted Answer · 2017-08-09 23:12:18Z

2

Try something like:

Dim sht As WorkSheet

Set sht = .Worksheets("SalesRep")

With sht.Sort
    .SortFields.Clear  '<<<<< clear any previous
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    for i=1 to myArrayLength
       SortFields.Add Key:=sht.Range(sht.cells(1,colNumArray(i)), _
                                     sht.cells(lastRow,colNumArray(i)))

       'maybe cleaner as
       'SortFields.Add Key:=sht.Cells(1, colNumArray(i)).Resize(lastRow, 1)
    next i
    .Apply
End With

You don't need the .Address in your range reference, but you do need to add the worksheet qualifier, or your code will fail when any other sheet is active.

answered Aug 9, 2017 at 23:12

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

wizlog Over a year ago

I didn't realize I had to re-reference the sheet within my referenced range. Thank you.

Collectives™ on Stack Overflow

Dynamic nested sorts using Excel VBA

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related