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
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