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.
It appears that you have a Unicode character 33710 (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.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With