VBA code to sort all cells that have data in specific columns?

[Event2020]

Hi everyone

I have the fillowing VBA code that sorts data on the active named spreasheet (Final Data 1)
using the search order as specified in the code.

For each column it sorts Cell 2 - 31282 but this does not allow for rows being removed or added
so I need Excel to select All cells with data and then perform the sort.

I have tried various combinations of

Range("A1", Range("A1").End(xlDown)).Sort Key1:=Range("A1"), Order1:=xlAscending

for column A but Excel does not like my combinations and I need to apply to to all of the Columns in my VBA.

Can some one point me in the right directection?

Sub SortRowsAlphabetically()

    ActiveWorkbook.Worksheets("Final Data 1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Final Data 1").Sort.SortFields.Add2 Key:=Range( _
        "A2:A31282"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("Final Data 1").Sort.SortFields.Add2 Key:=Range( _
        "D2:D31282"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortTextAsNumbers
    ActiveWorkbook.Worksheets("Final Data 1").Sort.SortFields.Add2 Key:=Range( _
        "E2:E31282"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("Final Data 1").Sort.SortFields.Add2 Key:=Range( _
        "J2:J31282"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Final Data 1").Sort
        .SetRange Range("A1:J31282")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
                
    End With
    
End Sub

 

[RobP]

Hi,

I added 2 lines, and amended your range formulas to take the "LR" (lastrow of data" number.

Would this do what you need ?

Also, I am assuming Col A always contains the last row of data. If ColA is sometimes not populated, but you have say Col C that is always populated, replace the (Rows.Count, 1) in my last row formula with a "3" for Col C (or for example "5" for Col E etc..)

Sub SortRowsAlphabetically()

    Dim LR As Long
    LR = ActiveWorkbook.Worksheets("Final Data 1").Cells(Rows.Count, 1).End(xlUp).Row 'define last row of data in Column 1 (A) as LR
    
    ActiveWorkbook.Worksheets("Final Data 1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Final Data 1").Sort.SortFields.Add2 Key:=Range("A2:A" & LR), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("Final Data 1").Sort.SortFields.Add2 Key:=Range("D2:D" & LR), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortTextAsNumbers
    ActiveWorkbook.Worksheets("Final Data 1").Sort.SortFields.Add2 Key:=Range("E2:E" & LR), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("Final Data 1").Sort.SortFields.Add2 Key:=Range("J2:J" & LR), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Final Data 1").Sort
        .SetRange Range("A1:J" & LR)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
                
    End With
    
End Sub

 

[kevin9999]

See if this gives you what you want.

Sub Multi_Sort()
    
   With Worksheets("Final Data 1").Sort
        .SortFields.Clear
        .SortFields.Add Key:=Range("A2"), Order:=1
        .SortFields.Add Key:=Range("D2"), Order:=1
        .SortFields.Add Key:=Range("E2"), Order:=1
        .SortFields.Add Key:=Range("J2"), Order:=1
        
        .SetRange Range("A2:J" & Cells.Find("*", , xlFormulas, , 1, 2).Row)
        .Apply
    End With
    
End Sub

 

[Event2020]

Thank you both for your suggestions and ultimately I went with the one from RobP

Thank you again.

 

[RobP]

Glad we could help - thanks for the feedback.

Rob