I am trying to sort a range in VBA and have tried numerous ways of identifying the range without success. I can't figure out why I get the run-time error on the sortfields.clear line (also when I inactivate this line I get the same error on the next line)
Sub Lease_Options()
Dim LastOptionRange As String
Range("A4").Select
ActiveCell.End(xlToRight).End(xlDown).Select
LastOptionRange = ActiveCell.Address(1, 1)
Range("$A$4:" & LastOptionRange).Sort.SortFields.Clear
Range("$A$4:" & LastOptionRange).Sort.SortFields.Add _
Key:=Range("C5", Range("C5").End(xlDown)), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
With Range("$A$4:" & LastOptionRange).Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
Bookmarks