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