sorting named range
 

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

sorting named range
 

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" wrote:

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

sorting named range
 

Thanks for your help

"Patrick Molloy" wrote in message
...
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" wrote:

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