How to filter in VBA for ''ő'' character?

Question

I'm trying to build a filter in excel that applies to all pages, however, due to my language, there's a character (ő) which I cannot enter into the VBA Editor.

Any ideas?

I'm currently using this code:

With Worksheets(q) ' With each worksheet selected in the looping process we apply the Autofilter with a specific criteria. We wish to filter out all persons whose name begins with H .Range("A1").AutoFilter field:=1, Criteria1:=Array("Bazsalikom", "Koriander", "Barna Rizs", "Jázmin Rizs", "Fafülgomba", "Csirke (elősütött", "Tofu (kockázott)", "Fejeskáposzta (csíkozott)", "Kínai kel (szeletelt)", "Szójacsíra", "Vöröshagyma (csíkozott)", "Marha (elősütött)", "Újhagyma (szeletelt)", "Sárgarépa (csíkozott)", "Karfiol (forrázott)", "Kápia Paprika", "Bambuszrügy (konzerv)", "Sertés (elősütött)", "Kacsa (elősütött)", "Rák (mirelit)", "Csiperke Gomba", "Cukkini (szeletelt)", "Kaliforniai Paprika", "Brokkoli (forrázott)", "Ananász (konzerv - ételhez)"), Operator:=xlFilterValues End With Next q End Sub

I highlighted the problematic items bold. I'm using Office 2016 on a mac.

Answer 1

It appears that you have a Unicode character 337₁₀ (base 10 rather than hex). Say we start with:

and apply an AutoFilter with:

Sub Macro99()
    Dim s As String

    s = ChrW(337)

    With Range("A1:A10")
        .AutoFilter
        .AutoFilter Field:=1, Criteria1:=s
    End With
End Sub

will produce:

You can apply this to any or all worksheets.

Answer 2

While you could use code for each of the entries that won't 'stick' in VBA (e.g. "l"&ChrW(337)&"sütött"), it would be easier to use some form of repository for your list. A worksheet makes a good choice and can be hidden later if you want to avoid displaying this to the user.

Optional: Create a dynamic named range with a Refers to: of
     =hidden!$A$2:index(hidden!$A:$A, match("žžž", hidden!$A:$A))

Build a variant array from the list and use the array elements as the filter.

Option Explicit

Sub keyedFilter()
    Dim fltr As Range, a As Long, arr As Variant

    'load the array
    With Worksheets("Hidden")
        With .Range(.Cells(2, "A"), .Cells(.Rows.Count, "A").End(xlUp))
            arr = Application.Transpose(.Cells.Value2)
        End With
    End With

    'filter the data
    With Worksheets("Sheet2")
        If .AutoFilterMode Then .AutoFilterMode = False
        With .Cells(1, 1).CurrentRegion
            '.AutoFilter field:=1, Criteria1:=dDICT.keys, Operator:=xlFilterValues
            .AutoFilter field:=1, Criteria1:=arr, Operator:=xlFilterValues
            With .Resize(.Rows.Count - 1, .Columns.Count).Offset(1, 0)
                If CBool(Application.Subtotal(103, .Cells)) Then

                    'there are filtered values.
                    'sample image was taken here

                End If
            End With
        End With
        If .AutoFilterMode Then .AutoFilterMode = False
    End With
End Sub

Sub reallyHideHidden()
    With Worksheets("Hidden")
        .Visible = xlVeryHidden
    End With
End Sub