CODEWITHSUNDEEP
excelvbasortingautofilterlistobject
Angus
Angus

Reputation: 31

Error refreshing Excel table filter and sorting with VBA

Within my workbook I have a simple worksheet ("Master") containing a table ("Table1") and a named range of two cells ("filter_input"). The table includes conditional formatting that highlights any rows containing the values of either cell in the "filter_input" range. the table is then sorted to place the highlighted rows at the top of the table. Manually refreshing the table sorting (Ctrl-Alt-L) works absolutely fine.

To automate this I have set Worksheet_change to identify changes within the "filter_input" range and if so, the filtering and sorting of the table should refresh automatically. I have had the following: 

Private Sub Worksheet_Change(ByVal target As Range)

Set isect_filter = Application.Intersect(target, Range("filter_input"))

If isect_filter Is Nothing Then

Else

    ActiveWorkbook.Worksheets("Master").ListObjects("Table1").AutoFilter.ApplyFilter
    With ActiveWorkbook.Worksheets("Master").ListObjects("Table1").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
        End With

    End If

End Sub

This code has been running with no issue for the past 12 months but upon opening the workbook today, triggering the code resulted in the run-time error:

Method 'AutoFilter' of object 'ListObject' failed.

Highlighting line:

ActiveWorkbook.Worksheets("Master").ListObjects("Table1").AutoFilter.ApplyFilter

I haven't changed anything in the code or document and the only change I can think of is that I recently received a new laptop and am running Office 365. I find it hard to believe that this would be the issue but I can't see what else has changed. I also have near identical code in a different workbook and that is also giving me the same error as of today.

If I comment out that line I receive a similar error referring to the following line and Method 'sort'. I've checked other ways to refresh the sorting but none of them seem to be working.

I've even tried using macro recorder to record the action of reapplying filter (or Ctrl-Alt-L) however the code it records seems wrong:

Sub Macro1()
'
' Macro1 Macro
'

'
    Range("C6:C7").DataFields
    Range("C7").
    ColorIndex.("Table1")..SubAddress
    With ThemeColor.("Master").("Table1").
        .Count = xlYes
        .ActiveSheet = False
        . = xlTopToBottom
        . = xlPinYin
        .
    End With
End Sub

EDIT 26/07/2018 - Second paste of code generated by Record Macro

Here is another paste of the code generated by Record Macro - this time no cell selection or anything other than hitting Ctrl-Alt-L to refresh the table. I've also run the same function via the ribbon menu (Home Tab > Editing > Sort & Filter > Reapply) and the code generated is the same.

Sub Macro3()
'
' Macro3 Macro
'

'
    ColorIndex.("Table1")..SubAddress
    With ThemeColor.("Master").("Table1").
        .Count = xlYes
        .ActiveSheet = False
        . = xlTopToBottom
        . = xlPinYin
        .
    End With
End Sub

Upvotes: 2

Views: 1052

Answers (1)

Angus
Angus

Reputation: 31

Well I've found a solution - although I'm not sure why my original code didn't work especially when it was working fine before.

Rather than performing actions on the table defined in-line, I defined the table as a ListObject variable first and performed the same action.

So the original code:

ActiveWorkbook.Worksheets("Master").ListObjects("Table1").AutoFilter.ApplyFilter
With ActiveWorkbook.Worksheets("Master").ListObjects("Table1").Sort
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

became:

Dim ActiveTable As ListObject
Set ActiveTable = ActiveWorkbook.Worksheets("Master").ListObjects("Table1")

ActiveTable.AutoFilter.ApplyFilter
With ActiveTable.Sort
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

Here is the full code for completeness:

Private Sub Worksheet_Change(ByVal target As Range)

    Set isect_filter = Application.Intersect(target, Range("filter_input"))

    If isect_filter Is Nothing Then

    Else
        Dim ActiveTable As ListObject
        Set ActiveTable = ActiveWorkbook.Worksheets("Master").ListObjects("Table1")

        ActiveTable.AutoFilter.ApplyFilter
        With ActiveTable.Sort
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End If

End Sub

I'd be grateful if anyone could explain the logic behind that to me.

Thanks

Upvotes: 1

Related Questions