CODEWITHSUNDEEP
vbaexcelsortingrange
wizlog
wizlog

Reputation: 302

Dynamic nested sorts using Excel VBA

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?

Upvotes: 0

Views: 174

Answers (1)

Tim Williams
Tim Williams

Reputation: 166331

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.

Upvotes: 2

Related Questions