Runtime 91 in autofilter.sort.sortfields

[jwarren73]

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.

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)
Else
    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
    .AutoFilter.Sort.SortFields.Clear
    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...

 

[DanteAmor]

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
        .Apply
      End With