Filter array containing regular expression or multiple critera

Question

I am looking to filter a column with anything that starts with either m,n,o. Things work fine when I tried to filter with 2 items but does not work for more than 2 items.

Criteria1:="=m*", Operator:=xlOr, Criteria2:="=n*"

Works fine to filter anything that starts with m or n

//Criteria1:="=p*", Operator:=xlOr, Criteria2:="=z*", Operator:=xlOr, Criteria2:="=e*"

Throws error invalid criteria

Criteria1:=Array("=mab", "=nab", "oab"), Operator:=xlFilterValues

Works fine to filter mab,nab,oab.

Criteria1:=Array("=m*", "=n*", "o*"), Operator:=xlFilterValues

Works fine to filter literally ``m*,n*,o*` (it is not being considered as regular expression), what i am looking is to filter anything that starts with m or n or o

Try with ^[mno]

Srdjan M.
– Srdjan M.

2018-02-28 14:03:31 +00:00
Commented Feb 28, 2018 at 14:03 — Srdjan M.
– Srdjan M., Commented Feb 28, 2018 at 14:03

YasserKhalil · Accepted Answer · 2018-02-28 14:23:34Z

Try using advanced filter in that way (suppose the field is column A)

Sub Test() Application.ScreenUpdating = False With Sheets("Sheet1") If .FilterMode Then .ShowAllData .Range("H2").Formula = "=OR(LEFT(A2,1)=""m"",LEFT(A2,1)=""n"",LEFT(A2,1)=""o"")" .Range("A1").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=.Range("H1:H2"), Unique:=False .Range("H2").ClearContents End With Application.ScreenUpdating = True End Sub

This looks great, thanks. But I observed that I am unable to perform undo using ctrl+z
As for running VBA macros there is no undo. You would make a reverse code that show all the data .. Notice the code and you can create such a code

Collectives™ on Stack Overflow

Filter array containing regular expression or multiple critera

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related