sorting named range

[inquirer]

I have defined a named range in vba
srt = Cells(1, i).Value & "_sort"
ActiveWorkbook.Names.Add Name:=srt, RefersTo:=Range(Cells(2, 6),
Cells(lastrow, 6))
where lastrow is 45
How can I use the range to sort the data?
(The worksheet name is "Data")
I have tried
Selection.Sort Key1:=ActiveSheet.Name!srt, Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

but this gives me and Object required error.
Could someone give me the correct way to enter the named range in the
selection command please?
Thanks
CHris

 

[Guest]

my table starts at D8 as width/depth not known

Option Explicit
Sub TestSort()
Dim RangeName As String
RangeName = "MySort"
With Range(Range("D8"), Range("D8").End(xlToRight).End(xlDown))
.Name = RangeName
End With
SortRange RangeName, 2
End Sub
Sub SortRange(WhichRange As String, col As Long)
Dim target As Range
Set target = Names.Item(WhichRange).RefersToRange
With target
.Sort .Cells(1, col), Header:=xlYes
End With
End Sub

This is a bit more generic in that the sort routine can be used several
times so sort differing ranges by other columns.

Can obviously be greatly enhanced but it is a demo

HTH
Patrick Molloy
Microsoft Excel MVP

 

[inquirer]

Thanks for your help

my table starts at D8 as width/depth not known

Option Explicit
Sub TestSort()
Dim RangeName As String
RangeName = "MySort"
With Range(Range("D8"), Range("D8").End(xlToRight).End(xlDown))
.Name = RangeName
End With
SortRange RangeName, 2
End Sub
Sub SortRange(WhichRange As String, col As Long)
Dim target As Range
Set target = Names.Item(WhichRange).RefersToRange
With target
.Sort .Cells(1, col), Header:=xlYes
End With
End Sub

This is a bit more generic in that the sort routine can be used several
times so sort differing ranges by other columns.

Can obviously be greatly enhanced but it is a demo

HTH
Patrick Molloy
Microsoft Excel MVP