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.

@gserg - That is not an appropriate duplicate. Due to the high incidence of invalid characters in a long list, the solution here is to use a Dictionary object loaded from the worksheet. Please reopen so I can offer a response.\ — , Sep 28 '16 at 13:40
@Jeeped I'm not yet used to the fact that it now closes as soon as I vote. — GSerg, Sep 28 '16 at 13:42
Possible duplicate of [How to type currency symbols in Visual Basic Editor](http://stackoverflow.com/q/24384952/11683) — GSerg, Sep 28 '16 at 13:43
Thanks (and yeah... it took me a while to get used to having a question go **Boom** like that!) — , Sep 28 '16 at 13:46

Gary's Student · Accepted Answer · 2016-09-28T14:33:14.120

5

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.

edited Sep 28 '16 at 14:33

answered Sep 28 '16 at 13:01

Gary's Student

95,722
10
59
99

2

Assuming that the windows regional settings are set to the appropriate code page, it can also be typed directly with Alt+0151. – Comintern Sep 28 '16 at 13:11
2

Great answer... but... it is a bit confusing IMO for people who might not understand your subscript of 10 to indicate that its the unicode value in decimal... still upvoted you (plus you beat me to it earlier!) – Robin Mackenzie Sep 28 '16 at 14:14
@RobinMackenzie Thanks for the comment...I adjusted the answer. – Gary's Student Sep 28 '16 at 14:33

score 3 · Answer 2 · 2016-09-28T14:44:05.650

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

I just saw your recent edit that included the fact that you are running this on a Mac. I'm not sure if you can use a Scripting.Dictionary on a Mac but you can build an array from the worksheet list. — , Sep 28 '16 at 14:19
Yup, [it won't work on Mac](http://stackoverflow.com/questions/5702362/vba-collection-list-of-keys/5702529#comment47733642_5702529). — GSerg, Sep 28 '16 at 14:29

score 2 · Answer 3 · answered Sep 28 '16 at 12:33

2

what I would do in your place would be to write the character in excel and to refer it from there. Write it in some kind of settings table.

answered Sep 28 '16 at 12:33

Vityata

42,633
8
55
100

score 1 · Answer 4 · answered Sep 28 '16 at 13:11

1

Only way is post it into a cell (say A1), then refer to that cell value to filter your range (say C1:F4) - ActiveSheet.Range("$C$1:$F$4").AutoFilter Field:=2, Criteria1:="=*" & Range("A1").Value & "*", Operator:=xlAnd

answered Sep 28 '16 at 13:11

Jeremy

1,337
3
12
26

score -1 · Answer 5 · answered Sep 28 '16 at 12:36

-1

Have you tried changing the font in the VBA editor.( Tools / Options / Editor Format) Switch to Times New Roman for your desired language and you should be able to enter the characters.

answered Sep 28 '16 at 12:36

wwn

563
5
9

2

VBA code modules are saved in ANSI. You can only enter characters that are supported by the current Windows codepage for non-Unicode programs. Excel cells, on the other hand, support Unicode, and you can put any characters there. – GSerg Sep 28 '16 at 13:33

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

5 Answers5