I am trying to perform a sort on a spreadsheet that is of varying lenght. There are 14 cells at the bottom that do not change and must remain at the bottom of the spreadsheet and unsorted. I am using a variable to determine the last row to be sorted. When I run the macro I get the error message "The sort reference is not valid. Make sure that it's within the data you want to sort, and the first Sort By box isn't the same or blank." Row 3 of the spreadsheet is my header row that I am using to sort by.
Dim LastRow As Long
LastRow = ActiveSheet.UsedRange.Rows.Count
Dim Final As Long
Final = LastRow - 14
Range("A3:P" & Final).Select
ActiveWorkbook.Worksheets("AAL ODR").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("AAL ODR").Sort.SortFields.Add(Range("H4:H" & Final), _
xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(0, 0, _
255)
ActiveWorkbook.Worksheets("AAL ODR").Sort.SortFields.Add(Range("H4:H" & Final), _
xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(0, 255 _
, 0)
ActiveWorkbook.Worksheets("AAL ODR").Sort.SortFields.Add(Range("H4:H" & Final), _
xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(255, _
255, 0)
ActiveWorkbook.Worksheets("AAL ODR").Sort.SortFields.Add(Range("G4:G" & Final), _
xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(252, _
213, 180)
ActiveWorkbook.Worksheets("AAL ODR").Sort.SortFields.Add Key:=Range("M4:M" & Final _
), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("AAL ODR").Sort.SortFields.Add Key:=Range("O4:O" & Final _
), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("AAL ODR").Sort.SortFields.Add Key:=Range("J4:J" & Final _
), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("AAL ODR").Sort
.SetRange Range("A3:P" & Final)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Bookmarks