Use VBA sort range on multiple columns...

[cboshdave]

I know this subject has been beat to death. I can't seem to find the specific answer I am looking for though. I need to sort blocks of data as I move down a spreadsheet.

The following code works (generally). It only sorts on the first column though. For example, if the block is C5:L8, I need it to sort on C5 Primary and E5 secondary. I thought that is what my rKeyRange is doing. But, it is not working. When I sort manually, it is working (originally, the strings were unsortable). Setting the range with Cells() seems easier to deal with since it is so dynamic.

Bottom line: Question is how to set the rKeyRange to sort on 2 columns and set it dynamically as I move down the sheet.

Private Sub RangeSort(iFirstRow As Long, iBlockSize As Long, iFirstCol As Long, iLastCol As Long)    Dim rDataRange As Range
    Dim rKeyRange As Range
    Set rDataRange = Range(Cells(iFirstRow, iFirstCol), Cells(iFirstRow + iBlockSize, iLastCol))
    rDataRange.Select
    Set rKeyRange = Range(Cells(iFirstRow, 3), Cells(iFirstRow, 5))
    rDataRange.Sort Key1:=rKeyRange, Order1:=xlAscending
End Sub

 

[RickXL]

Hi,

To sort on two columns you just need to add another key - Key2:= ...

rDataRange.Sort Key1:=rKeyRange, Order1:=xlAscending, Key2:=rKeyRange2

However, it looks as if you are sorting all the blocks by the same columns so why not just add a column so that you can assign a unique identifier to each block? You could then just sort on that ID and the other two columns and you would only need one sort.

 

[cboshdave]

Hi,

To sort on two columns you just need to add another key - Key2:= ...

rDataRange.Sort Key1:=rKeyRange, Order1:=xlAscending, Key2:=rKeyRange2

However, it looks as if you are sorting all the blocks by the same columns so why not just add a column so that you can assign a unique identifier to each block? You could then just sort on that ID and the other two columns and you would only need one sort.

You are absolutely correct. I could sort once, but the data in the other columns is not contiguous, so I would get screwy results. This is the best I can come up with and that DID solve my problem. I forgot it had to be multiple keys. I was trying to load multiple values into one key. Thanks!!

 

[RickXL]

OK - no problem and thanks for the feedback.