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
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