Data sort on dynamic range

[Callum2010]

Hi

I was after some assitance performing a data sort on a range that is dynamic as opposed to fixed. However the range A21:AA755 will grow over time.

Does anyone have any idea?

Currently i have the following code

Sub Sort()
' Sort Macro
Application.ScreenUpdating = False
Sheet1.Unprotect Password:="maso1975"
Range("A21:AA755").Sort Key1:=Range("S22"), Order1:=xlAscending, Key2:= _
Range("Q22"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=6, _
MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal, Key3:=Range("N22"), Order1:=xlAscending
Sheet1.Protect Password:="maso1975", AllowFiltering:=True, DrawingObjects:=False
Application.Goto Reference:=Range("Q22"), Scroll:=True
ActiveSheet.Range("S21").Select
Application.ScreenUpdating = True
End Sub

 

[VoG]

Try

Sub aSort()
' Sort Macro
Dim LR As Long
Application.ScreenUpdating = False
Sheet1.Unprotect Password:="maso1975"
LR = Range("A" & Rows.Count).End(xlUp).Row
Range("A21:AA" & LR).Sort Key1:=Range("S22"), Order1:=xlAscending, Key2:= _
Range("Q22"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=6, _
MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal, Key3:=Range("N22"), Order1:=xlAscending
Sheet1.Protect Password:="maso1975", AllowFiltering:=True, DrawingObjects:=False
Application.Goto Reference:=Range("Q22"), Scroll:=True
ActiveSheet.Range("S21").Select
Application.ScreenUpdating = True
End Sub

You should avoid using VBA keywords like Sort as the names of macros.

 

[Callum2010]

Thanks works perefectly