1

I have a table that I use as a glorified to-do list.

enter image description here

I have recorded a macro to sort column F so that the grey cells (i.e. tasks that are active) are at the top, and to filter any tasks marked as "Complete" (column E) are hidden. See code below:

Sub Sort()

    ActiveSheet.ListObjects("Table4").Range.AutoFilter Field:=4, Criteria1:= _
        Array("Active", "On Hold", "="), Operator:=xlFilterValues
    ActiveWorkbook.Worksheets("My Tasks").ListObjects("Table4").Sort.SortFields. _
        Clear
    With ActiveWorkbook.Worksheets("My Tasks").ListObjects("Table4").Sort. _
        SortFields.Add(Range("Table4[[#All],[Hold Reason]]"), xlSortOnCellColor, _
        xlAscending, , xlSortNormal)
        .SortOnValue.Color = RGB(255, 255, 255)
        .SortOnValue.PatternColor = RGB(208, 206, 206)
        .SortOnValue.Pattern = 7
    End With

    With ActiveWorkbook.Worksheets("My Tasks").ListObjects("Table4").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

End Sub

I tried to run this code but Excel crashed with no error message. I tried separating the code into three individual macros:

Sub SortAndFilter()

    Sort
    Filter
    
End Sub

Sub Sort()

    ActiveSheet.ListObjects("Table4").Range.AutoFilter Field:=4, Criteria1:= _
        Array("Active", "On Hold", "="), Operator:=xlFilterValues
    ActiveWorkbook.Worksheets("My Tasks").ListObjects("Table4").Sort.SortFields. _
        Clear
    With ActiveWorkbook.Worksheets("My Tasks").ListObjects("Table4").Sort. _
        SortFields.Add(Range("Table4[[#All],[Hold Reason]]"), xlSortOnCellColor, _
        xlAscending, , xlSortNormal)
        .SortOnValue.Color = RGB(255, 255, 255)
        .SortOnValue.PatternColor = RGB(208, 206, 206)
        .SortOnValue.Pattern = 7
    End With

End Sub

Sub Filter()

    With ActiveWorkbook.Worksheets("My Tasks").ListObjects("Table4").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

End Sub

but this still caused the same crash. When I used VBA's 'step into' function, I saw that the first section (that sorts the rows) runs as expected, but when the code reaches Ln27 (".Apply"), the crash occurs.

I think the problem is with the code preceding Ln27, and that it's only when Ln27 is executed that whatever the problem is manifests itself in the form of a crash. Please help!

Alternatively, is there another way to sort and filter the table using only one button (or macro assigned to a shape)?

Thanks! :)

Sabahat Hussain Qureshi
  • 1,176
  • 3
  • 14
BearOfVeryLittleBrain
  • 13
  • 2
  • How big is your data? How many columns? How many rows? Do you have a lot of formulas that may use volatile functions? Did you try sorting deactivating calculation using Manual Mode? Also, check https://stackoverflow.com/questions/29896890/excel-vba-sort-error-1004 – Foxfire And Burns And Burns Sep 08 '22 at 12:47
  • My assumption is, this is due to auto calculations. Try to put this as first line in the code:Application.Calculation = xlCalculationManual. If it works you only need to set it to automatic at the end of macro. I do not have time to test myself now so this is why it only is a hint... – Aldert Sep 08 '22 at 12:58

0 Answers0