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...
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
7,919
Office Version
2007
Platform
Windows
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

 

Forum statistics

Threads
1,078,437
Messages
5,340,278
Members
399,362
Latest member
iayb

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...
Top