Runtime 91 in autofilter.sort.sortfields


New Member
Jan 22, 2015
I'm running into an issue with a sort command, that I'm sure I'm missing something simple and just overthinking.... any help appreciated figuring out what went wrong. Using Excel 2016.

Rich (BB code):
Public Const EnrollmentCol              As String = "AF"

Sub ImportSort()

Dim CompleteWBFullName As String
Dim FiltRng As Range, VisRng As Range
Dim i As Long, j As Long, LastRow As Long, LastCol As Long, Col As Long
Dim ArrIndex As Long, DistrictsArrCount As Long, ParentIDFullArrCount As Long, DistrictEnrolCount As Long, DistrictDummyCount As Double
Dim CMOSchoolsInDistrictsCount As Long

Dim CMOSelfSchoolsArr As Variant, CMOSchoolsInDistrictsArr As Variant, CMODistrictsArr As Variant

Dim PIDFullArr As Variant, PIDSortedbyEnrolArr As Variant, DisrictSubDisrictSortedbyEnrolArr As Variant, EnrolCounterRaise As Double
Dim ParentIDFullArr As Variant
Dim NewFullArr As Variant

' show open file dialog file to select the "Complete" file
CompleteWBFullName = GetFileName(FolderPathRng.Value2, "Complete file", "Custom Excel Files", "*.csv, *.xls, *.xlsx, *xlsm")

If CompleteWBFullName <> "" Then
    Set CompleteWB = Workbooks.Open(Filename:=CompleteWBFullName, ReadOnly:=False)
    Exit Sub
End If
Set CompleteDBSht = CompleteWB.Worksheets(1) ' make sure there's only 1 sheet

Application.ScreenUpdating = False

With CompleteDBSht
    If .AutoFilterMode Then .AutoFilter.ShowAllData ' also Remove Auto-Filter if it's on

    LastRow = FindLastRow(CompleteDBSht)
    LastCol = FindLastCol(CompleteDBSht)
    ' add 5 extra columns on the right: Enrollment Dummy ; Hierarchy ; Dummy PID ; Sort Order ; 1 (for District or Sub-District)
    ReDim NewFullArr(1 To LastRow, 1 To CompleteLastCol + 5)
    On Error Resume Next
    On Error GoTo 0

    .AutoFilter.Sort.SortFields.Add Key:=Range(EnrollmentCol & "1:" & EnrollmentCol & LastRow), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
The final, blue row is where the macro hits the 91 error - any ideas? Several of the other variables are set as public elsewhere in the system, including the EnrollmentCol setting. I've been working on this for a few hours, and know I'm overlooking something basic...


Well-known Member
Dec 3, 2018
Office Version
You can change the line for this:

    Range(EnrollmentCol & "1:" & EnrollmentCol & LastRow).Sort key1:=Range(EnrollmentCol & "1"), order1:=xlDescending, Header:=xlYes

Or for this:

      With .Sort
        .SortFields.Add Key:=Range(EnrollmentCol & "2:" & EnrollmentCol & LastRow), _
            SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
        .SetRange Range(EnrollmentCol & "1:" & EnrollmentCol & LastRow)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
      End With


Forum statistics

Latest member

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...