Runtime 91 in autofilter.sort.sortfields

jwarren73

New Member
Joined
Jan 22, 2015
Messages
37
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)
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...
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
You can change the line for this:


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


-----------------
Or for this:

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

 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,908
Members
448,532
Latest member
9Kimo3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top