Results 1 to 2 of 2

Thread: Runtime 91 in autofilter.sort.sortfields
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jan 2015
    Post Thanks / Like
    0 Post(s)
    0 Thread(s)

    Default Runtime 91 in autofilter.sort.sortfields

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

  2. #2
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Post Thanks / Like
    66 Post(s)
    14 Thread(s)

    Default Re: Runtime 91 in autofilter.sort.sortfields

    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

    Regards Dante Amor

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts