PDA

View Full Version : [SOLVED] Sort Named Range with VBA



petrovich17
07-06-2017, 02:29 PM
Hi All,

I am attempting to sort a named range with VBA. I have code that creates and sets the range, and code that sorts it. I have printed the range to the immediate window and the range is correct. However, when I try to sort using the named range, I get an error that says, "Run time error '1004': Method 'Range' of object '_Worksheet' failed." If I run the same code with the range manually typed in (ie. sht.Range("ottab") replaced with sht.Range("A1:V31") the code works correctly. Any help in troubleshooting this issue would be greatly appreciated.



Sub sortTest()
'
' sortTest Macro


Dim ottab As Range
Dim sht As Worksheet, bottomMostRow As Long, rightMostColumn As Long
Set sht = ActiveSheet
With sht
bottomMostRow = .Cells(1, 1).End(xlDown).Row
rightMostColumn = .Cells(1, 1).End(xlToRight).Column
Set ottab = .Range(.Cells(1, 1), .Cells(bottomMostRow, rightMostColumn))
End With


Debug.Print ottab.Address(external:=False)


sht.Sort.SortFields.Clear
sht.Range("ottab").Sort Key1:=sht.Range("V1"), Order1:=xlAscending, _
Header:=xlYes

End Sub

mdmackillop
07-06-2017, 02:42 PM
ottab is a range variable, not a range name

ottab.Sort Key1:=sht.Range("V1"), Order1:=xlAscending, _
Header:=xlYes

petrovich17
07-06-2017, 03:05 PM
Thanks very much. This is my first foray into writing VBA so I thought it would be something simple like that. Greatly appreciated.