Sort Macro

[skhou]

Hi,

I have the following VB code whereby I am sorting the columns B, then columns A by alphabetical order. The current code below works fine because the range has been hardcoded to be 53 rows. However, the rows will always change, so I am just wondering how I can tweak the code so it recognises to capture whatever the current number of rows is at the time? Thanks

Sub Sort()

ActiveWorkbook.Worksheets("Entity_Identifier_Function").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Entity_Identifier_Function").Sort.SortFields.Add _
Key:=Range("B2:B53"), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal


ActiveWorkbook.Worksheets("Entity_Identifier_Function").Sort.SortFields.Add _
Key:=Range("A2:A53"), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal


With ActiveWorkbook.Worksheets("Entity_Identifier_Function").Sort
.SetRange Range("A1:B53")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

 

[AlphaFrog]

This will reference A2 to the last used row in column A

Sub Sort()

    [COLOR="Red"]Dim Lastrow As Long
    
    Lastrow = Range("A" & Rows.Count).End(xlUp).Row[/COLOR]
    
    ActiveWorkbook.Worksheets("Entity_Identifier_Function").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Entity_Identifier_Function").Sort.SortFields.Add _
            Key:=Range([COLOR="Red"]"B2:B" & Lastrow[/COLOR]), SortOn:=xlSortOnValues, Order:=xlAscending, _
            DataOption:=xlSortNormal


    ActiveWorkbook.Worksheets("Entity_Identifier_Function").Sort.SortFields.Add _
            Key:=Range([COLOR="Red"]"A2:A" & Lastrow[/COLOR]), SortOn:=xlSortOnValues, Order:=xlAscending, _
            DataOption:=xlSortNormal


    With ActiveWorkbook.Worksheets("Entity_Identifier_Function").Sort
        .SetRange Range([COLOR="Red"]"A1:A" & Lastrow[/COLOR])
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub