Sorting - use of variable sort key

[BernzG]

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

 

[BernzG]

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