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
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    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.

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

  2. #2
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,187
    Post Thanks / Like
    Mentioned
    66 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Runtime 91 in autofilter.sort.sortfields

    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

    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
  •