VBA - Two sort Criterias

[ExcelMercy]

Hey all,


I need to sort first by column B in Ascending order, and then by column C in Ascending order.


I used the record a macro and went through custom sort... It works. Issue is, the range will not always be the same. I was curious if it would be possible to apply the sort through all of the fields from A:GK regardless how many fields?

 

[JoeMo]

Hey all,


I need to sort first by column B in Ascending order, and then by column C in Ascending order.


I used the record a macro and went through custom sort... It works. Issue is, the range will not always be the same. I was curious if it would be possible to apply the sort through all of the fields from A:GK regardless how many fields?

Provided there are no empty columns between A:GK, you can sort on B and C and all columns will be affected. Is that what you mean by "sort through all of the fields"?

 

[ExcelMercy]

I mean, when I do this:

    ActiveWorkbook.Worksheets("Main_Sheet").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Main_Sheet").Sort.SortFields.Add Key:=Range( _
        "D2:D2904"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("Combined OBF").Sort.SortFields.Add Key:=Range( _
        "I2:I2904"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Combined OBF").Sort
        .SetRange Range("A1:GK2904")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

It won't always be 2904 entries. Plus the recorded macro always makes things slow :/

 

[FryGirl]

Here's an example

Not sure why you have the names of two different worksheets in this one sort macro???

This macro sorts on the activesheet

Change the part in red to whatever column you want to use to establish the last row

Sub SortData()
Dim LR As Long
  LR = Range("[COLOR="#FF0000"]A[/COLOR]" & Rows.Count).End(xlUp).Row
    With ActiveSheet.Sort
        .SortFields.Clear
        .SortFields.Add Key:=Range("D2:D" & LR), SortOn:=xlSortOnValues, Order:=xlAscending
        .SortFields.Add Key:=Range("I2:I" & LR), SortOn:=xlSortOnValues, Order:=xlAscending
        .SetRange Range("A1:GK" & LR)
        .Header = xlYes
        .Orientation = xlTopToBottom
        .Apply
    End With
End Sub

 

[ExcelMercy]

ah, yea, it's all one sheet: Main_Sheet

---


Works great thanks a ton!

 

[FryGirl]

You are very welcome. We are happy to help.