vba Range.Autofilter fails on Excel 2011 (Mac)

I have a public following sub in my code as shown below:

Public Sub ResetFilters(ByRef tbl As ListObject)
    With tbl

        '// If Filter Arrows are OFF - turns them on
        '// If Filter Arrows are ON - turns them off and resets filter
        .Range.AutoFilter

        '// Always turns filter arrows to on and sorts table by first field
        .Range.AutoFilter Field:=1
    End W开发者_如何学编程ith
End Sub

As you can See I make use of Excel Tables (ListObjects in vba speak) so I pass the reference into the sub, and it is supposed to reset the table to an unfiltered state. It works fine on a PC with Excel 2007 but fails on Excel 2011 on a Mac with:

Method 'Autofilter' of Object 'Range' failed

The following link for Excel 2011 VBA Reference shows the AutoFilter Method of the Range object and it matches what I can see in the references for Excel 2007 VBA References.

So can anyone see why this is failing?

I was not able to get your ListObject.ShowAutoFilter workaround to work for me, especially because I needed to not only turn off autofilter, but then programmatically restore the filter once my code was done.

I did a little messing around with macro recording on the Mac and found that even though Range.AutoFilter throws an error Selection.AutoFilter doesn't. So I was able to just select the range I wanted to filter then apply my filter to the selection.

    ws.Range(currentFiltRange).Select
    Selection.AutoFilter

If you need to preserve the user's selection you can easily restore that as well, here is my complete sub routine to save the autoFilter state, run your own code, then restore the autofilter state and it works on both PC and Mac.

Private Sub saveAndRestoreAutoFilterPCandMAC()


    Application.ScreenUpdating = False

    'START SAVING AUTOFILTER STATE
    Dim ws As Worksheet
    Dim filterArray()
    Dim currentFiltRange As String
    Dim col As Integer
    Dim usingAutoFilter As Boolean
    Dim userSelection As String

    usingAutoFilter = False      
    Set ws = ActiveSheet

    'Capture AutoFilter settings
    If ws.AutoFilterMode = True Then
        With ws.AutoFilter
            currentFiltRange = .Range.Address
            If ws.FilterMode = True Then
                usingAutoFilter = True
                With .Filters
                    ReDim filterArray(1 To .count, 1 To 3)
                    For col = 1 To .count
                        With .Item(col)
                            If .On Then
                                filterArray(col, 1) = .Criteria1
                                If .Operator Then
                                    filterArray(col, 2) = .Operator
                                    If .Operator = xlAnd Or .Operator = xlOr Then
                                        filterArray(col, 3) = .Criteria2
                                    End If
                                End If
                            End If
                        End With
                    Next col
                End With
            End If
        End With
    End If
    'END SAVING AUTOFILTER STATE

    'Remove AutoFilter
    ws.AutoFilterMode = False

    'Start Your code here

    'End of your code

    'START RESTORE FILTER SETTINGS
    If Not currentFiltRange = "" Then
        userSelection = Selection.Address
        ws.Range(currentFiltRange).Select
        Selection.AutoFilter
        If usingAutoFilter Then
            For col = 1 To UBound(filterArray(), 1)
                If Not IsEmpty(filterArray(col, 1)) Then
                    If filterArray(col, 2) Then
                        'check if Criteria2 exists and needs to be populated
                        If filterArray(col, 2) = xlAnd Or filterArray(col, 2) = xlOr Then
                            ws.Range(currentFiltRange).Select
                            Selection.AutoFilter Field:=col, _
                                Criteria1:=filterArray(col, 1), _
                                Operator:=filterArray(col, 2), _
                                Criteria2:=filterArray(col, 3)
                        Else
                            ws.Range(currentFiltRange).Select
                            Selection.AutoFilter Field:=col, _
                                Criteria1:=filterArray(col, 1), _
                                Operator:=filterArray(col, 2)
                        End If
                    Else
                        ws.Range(currentFiltRange).Select
                        Selection.AutoFilter Field:=col, _
                        Criteria1:=filterArray(col, 1)
                    End If
                End If
            Next col
        End If
    End If
    ws.Range(userSelection).select

    'END RESTORE FILTER SETTINGS

    Application.ScreenUpdating = True

End Sub

It doesnt seem there have been alot of people jumping at this question... At any rate, if anyone else is interested, I think I found a workaround using the ListObject.ShowAutoFilter property. It is a read/write boolean property and when turned off, it will reset the filters in the ListObject. It has the added bonus of working on both Excel 2011 for Mac and Excel 2007 (and 2010 for that matter) on PC's.