Change Recorded Macro To Used Range

[Dazzawm]

I have recorded the macro below but I want to use it on different files with different amount of rows. What do I need to do for it to work on a used range rather than a set amount of rows like below please? Also the name of the sheet to be active sheet rather than a named sheet.

Sub Macro5()
    ActiveWorkbook.Worksheets("Sheet").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet").Sort.SortFields.Add2 Key:=Range("A2:A2456" _
        ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet").Sort
        .SetRange Range("A1:T2456")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("A1").Select
End Sub

 

[SanjayGMusafir]

I have recorded the macro below but I want to use it on different files with different amount of rows. What do I need to do for it to work on a used range rather than a set amount of rows like below please? Also the name of the sheet to be active sheet rather than a named sheet.

Sub Macro5()
    ActiveWorkbook.Worksheets("Sheet").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet").Sort.SortFields.Add2 Key:=Range("A2:A2456" _
        ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet").Sort
        .SetRange Range("A1:T2456")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("A1").Select
End Sub

For Name of active sheet - instead of .Worksheets("Sheet") Change to .ActiveSheet

for variable number of rows - use structured tables, in that case the range becomes the TableName and you can sort values by any column head you wish to

Even that would have limitation if table name changes in every file.

 

[Dazzawm]

For Name of active sheet - instead of .Worksheets("Sheet") Change to .ActiveSheet

for variable number of rows - use structured tables, in that case the range becomes the TableName and you can sort values by any column head you wish to

Even that would have limitation if table name changes in every file.

'
for variable number of rows - use structured tables, in that case the range becomes the TableName and you can sort values by any column head you wish to

Even that would have limitation if table name changes in every file.'

Sorry don't understand any of this.

 

[SanjayGMusafir]

'
for variable number of rows - use structured tables, in that case the range becomes the TableName and you can sort values by any column head you wish to

Even that would have limitation if table name changes in every file.'

Sorry don't understand any of this.

Convert your data on a sheet to a table using Insert Tab - Insert Table

This converts your data to structured table. remember to name that table in Table Design Tab.

Hope that helps

 

[Dazzawm]

I thought it may be something similar to below but I don't know where to put it etc,,,

lr = .Range("A" & .Rows.Count).End(xlUp).Row

 

[SanjayGMusafir]

I thought it may be something similar to below but I don't know where to put it etc,,,

lr = .Range("A" & .Rows.Count).End(xlUp).Row

try this

Dim lr as Range

ActiveWorkbook.Activesheet.Sort.SortFields.Clear

ActiveWorkbook.Activesheet.Sort.SortFields.Add2 Key:=Range("lr" _
        ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet").Sort
        .SetRange Range("lr")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

 

[Joe4]

try this

Dim lr as Range

ActiveWorkbook.Activesheet.Sort.SortFields.Clear

ActiveWorkbook.Activesheet.Sort.SortFields.Add2 Key:=Range("lr" _
        ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet").Sort
        .SetRange Range("lr")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

I don't think that will work at all, as "lr" is just a single row number, not a range address.

 

[Dazzawm]

try this

Dim lr as Range

ActiveWorkbook.Activesheet.Sort.SortFields.Clear

ActiveWorkbook.Activesheet.Sort.SortFields.Add2 Key:=Range("lr" _
        ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet").Sort
        .SetRange Range("lr")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

That didn't work. When I debug it points to..
ActiveWorkbook.Worksheets("Sheet").Sort.SortFields.Add2 Key:=Range("lr" _
), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

 

[Dazzawm]

Don't worry about it, I will just change the numbers in the recorded code.

 

[Joe4]

Does this do what you want?

Sub MySort()

    Dim lr As Long
    Dim rng1 As Range
    Dim rng2 As Range
    
'   Find last row with data in column A
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    
'   Set ranges
    Set rng1 = Range("A2:A" & lr)
    Set rng2 = Range("A1:T" & lr)
    
    ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add2 Key:=rng1, _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveSheet.Sort
        .SetRange rng2
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("A1").Select
    
End Sub