Excel VBA AutoFilter Function not working on Macs

[Xyleene]

Hello!
I've created a file which has some VBA Script for Autofilter search boxes and a Reset filter function. I've tested the file with several users and have found everything works perfectly on Windows but does not work on someone using Excel - Mac. The Mac users receive a Microsoft Visual Basic 400 error. I've searched every possible thing I could think of online, but am unable to figure out why a Mac user can not use this script:

[B]Public BaseFltr As Integer[/B]

Sub Filter()
'
Application.ScreenUpdating = False

    Range("A19").Select
    ActiveWindow.FreezePanes = True

    Rows("18:18").Select
'    Range("C18").Activate
    Selection.EntireRow.Hidden = True

With Sheet1
        .AutoFilterMode = False
        With .Range("$E$18:$AJ$111111")
            .AutoFilter
            If Range("B6").Value <> "" Then .AutoFilter Field:=1, Criteria1:="=*" & Range("B6").Value & "*"
            If Range("B7").Value <> "" Then .AutoFilter Field:=2, Criteria1:="=*" & Range("B7").Value & "*"
        End With
End With         
End Sub


[B]Sub AddtnlFilters()[/B]
'
Application.ScreenUpdating = False

    Range("A19").Select
    ActiveWindow.FreezePanes = True

    Rows("18:18").Select
'    Range("C18").Activate
    Selection.EntireRow.Hidden = True

        With Sheet1
                    .AutoFilterMode = False
                    With .Range("$A$18:$AJ$111111")
                        .AutoFilter
                        If Range("B6").Value <> "" Then .AutoFilter Field:=5, Criteria1:="=*" & Range("B6").Value & "*"
                        If Range("B7").Value <> "" Then .AutoFilter Field:=6, Criteria1:="=*" & Range("B7").Value & "*"
                        If Range("B11").Value <> "" Then .AutoFilter Field:=3, Criteria1:="=*" & Range("B11").Value & "*"
                        If Range("B12").Value <> "" Then .AutoFilter Field:=4, Criteria1:="=*" & Range("B12").Value & "*"
                        If Range("B13").Value <> "" Then .AutoFilter Field:=13, Criteria1:="=*" & Range("B13").Value & "*"
                        If Range("B14").Value <> "" Then .AutoFilter Field:=17, Criteria1:="=*" & Range("B14").Value & "*"
                        If Range("B15").Value <> "" Then .AutoFilter Field:=18, Criteria1:="=*" & Range("B15").Value & "*"
                        If Range("D11").Value <> "" Then .AutoFilter Field:=21, Criteria1:="=*" & Range("D11").Value & "*"
                        If Range("D12").Value <> "" Then .AutoFilter Field:=22, Criteria1:="=" & Range("D12").Value & ""
                        If Range("D13").Value <> "" Then .AutoFilter Field:=16, Criteria1:="=*" & Range("D13").Value & "*"
                        If Range("D14").Value <> "" Then .AutoFilter Field:=30, Criteria1:="=*" & Range("D14").Value & "*"
                        If Range("D15").Value <> "" Then .AutoFilter Field:=36, Criteria1:="=*" & Range("D15").Value & "*"

                    End With
        End With
'End If
End Sub


[B]Sub ResetFilters()[/B]
'
Application.ScreenUpdating = False

    Range("A19").Select
    ActiveWindow.FreezePanes = True

    Rows("18:18").Select
'    Range("C18").Activate
    Selection.EntireRow.Hidden = True

               
        Range("B6:B7").ClearContents
        Range("B11:B15").ClearContents
        Range("D11:D15").ClearContents
                        
        Range("B6").Select
               With Sheet1
                    .AutoFilterMode = False
                End With
End Sub

Hopefully someone can help

 

[mole999]

MAC dosen't implement the full VBA of windows. What do you get if your record the action on the MAC

 

[Xyleene]

Forgive me, I'm unclear on the ask. Do you mean if I create the file on Mac first? I personally don't have a Mac but was able to sit down with someone and run through the script. It is providing a 1004 error at this section:

With Sheet1
.AutoFilterMode = False
With .Range("$E$18:$AJ$111111")
.AutoFilter
If Range("B6").Value <> "" Then .AutoFilter Field:=1, Criteria1:="=*" & Range("B6").Value & "*"
If Range("B7").Value <> "" Then .AutoFilter Field:=2, Criteria1:="=*" & Range("B7").Value & "*"

 

[mole999]

> https://answers.microsoft.com/en-us...al-excel/3dfdb577-d9a9-41a8-94c5-42478d0e4ed1

 

[Xyleene]

I feel like such a newbie and probably have no claim to saying I know VB. I've read through the answer which resolves this but it's greek to me.

 

[Xyleene]

I'm hoping this thread isn't closed. After doing some more research I was able to find some script that works on a Mac, but need some advise on how to write the following:

1. Point script to search in A2 but as a wildcard
2. If noting listed in A2 search in B2 instead as a wildcard

Option Explicit

Sub AutoFilterData()

Dim wsData As Worksheet
Set wsData = ThisWorkbook.Worksheets("Flat-All")
With wsData
On Error Resume Next
'//Reset AutoFilter

.ShowAllData

.Range("A9:AG9").AutoFilter 5, .Range("A2")
.Range("A9:AG9").AutoFilter 5, .Range("B2")

End With


'//Close my objects
Set wsData = Nothing
End Sub

 

[bmont]

I got around this by cycling through the list objects on my worksheet and resetting to "All" by omitting the Criteria1 parameter per list object.
Note this sentence "If this argument is omitted, the criteria is All" here:

Range.AutoFilter method (Excel)

Office VBA reference topic

docs.microsoft.com

Sub FilterFor(ButtonSelection As String)
'User clicks a button on-screen to select what to filter for across n>1 list objects on the worksheet.
'That button-click sends the string to this sub.

    Dim ws As Worksheet    
    Set ws = ThisWorkbook.Worksheets("Inputs")
    
    'Cycle through listobjects
    Dim lo As ListObject

                For Each lo In ws.ListObjects
                    If ButtonSelection = "Both" Then
                        Range(lo).ListObject.ListColumns("ColumnNameToBeFiltered").Range.AutoFilter (1)  '<-- omit Criteria1 here to select All per the above link.
                    Else
                        Range(lo).ListObject.ListColumns("ColumnNameToBeFiltered").Range.AutoFilter field:=1, Criteria1:=ButtonSelection
                    End If
                Next lo
    
End Sub