Sorting - use of variable sort key

Hi,

I am trying to build a macro that will sort three sets of data in the same sheet. The difficulty is that the sort key will vary each time as data may have been added or removed from each table and thus the row number of the sort key will change.

I tried setting a "name" as the sort key but if this row is deleted then I lose the name and the macro won't work.


Sub SSB_Name_Sort()

Application.ScreenUpdating = False

Range("SortSSBs1").Select
Selection.Sort Key1:=Range("A3:A3"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Range("SortSSBs2").Select
Selection.Sort Key1:=Range("A44:A44"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Range("SortSSBs3").Select
Selection.Sort Key1:=Range("A85:A85"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("A1").Select


End Sub

Can someone help me here please.

Cheers,
Bernz

Hi,

Found a solution myself now.

Made the Header as NO and made the Sort Key the same as the range to be sorted.

This is what I now have and it seems to work okay.

Sub SSB_Name_Sort()


Application.ScreenUpdating = False

Range("SortSSBs1").Select
Selection.Sort Key1:=Range("SortSSBs1"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Range("SortSSBs2").Select
Selection.Sort Key1:=Range("SortSSBs2"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Range("SortSSBs3").Select
Selection.Sort Key1:=Range("SortSSBs3"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("A1").Select


End Sub