VBA sorting columns with unknown number of rows

[LNG2013]

I need help I have Data on sheet "Data" in Columns A:AD with an undetermined number of rows.
In the code I recorded below it says A2:A52 but it changes depending on the data that is imported
Columns are C, E, F, and I.

Sub Macro1()
'
' Macro1 Macro
'

'
    Sheets("Data").Select
    Cells.Select
    ActiveWorkbook.Worksheets("Data").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Data").Sort.SortFields.Add Key:=Range("C2:C52"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Data").Sort.SortFields.Add Key:=Range("E2:E52"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Data").Sort.SortFields.Add Key:=Range("F2:F52"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Data").Sort.SortFields.Add Key:=Range("I2:I52"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Data").Sort
        .SetRange Range("A1:AD52")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

 

[Alex Blakenburg]

See if this works for you.

Sub SortMacro1()
'
' Macro1 Macro
'

'
    Dim shtData As Worksheet
    Dim lastRow As Long
    
    Set shtData = Worksheets("Data")
    lastRow = shtData.Range("A" & Rows.Count).End(xlUp).Row

    With shtData
        .Sort.SortFields.Clear
        .Sort.SortFields.Add Key:=.Range("C2:C" & lastRow), _
              SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .Sort.SortFields.Add Key:=.Range("E2:E" & lastRow), _
              SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .Sort.SortFields.Add Key:=.Range("F2:F" & lastRow), _
              SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .Sort.SortFields.Add Key:=.Range("I2:I" & lastRow), _
              SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    End With
    
    With shtData.Sort
        .SetRange Range("A1:AD" & lastRow)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

 

[LNG2013]

See if this works for you.

Sub SortMacro1()
'
' Macro1 Macro
'

'
    Dim shtData As Worksheet
    Dim lastRow As Long
   
    Set shtData = Worksheets("Data")
    lastRow = shtData.Range("A" & Rows.Count).End(xlUp).Row

    With shtData
        .Sort.SortFields.Clear
        .Sort.SortFields.Add Key:=.Range("C2:C" & lastRow), _
              SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .Sort.SortFields.Add Key:=.Range("E2:E" & lastRow), _
              SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .Sort.SortFields.Add Key:=.Range("F2:F" & lastRow), _
              SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .Sort.SortFields.Add Key:=.Range("I2:I" & lastRow), _
              SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    End With
   
    With shtData.Sort
        .SetRange Range("A1:AD" & lastRow)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

That works ty!

 

[Alex Blakenburg]

You're welcome. Glad I could help.