Excel Text Filtering: Multiple Text Contains

[beastmode100]

Hi Guys,

Just wanna ask if you could help me with this, I am currently automating a process that needs to filter a text that contains a particular text and other texts.

Example Code:

ActiveSheet.Range("$A$1:$B$50000").AutoFilter Field:=1, Criteria1:=Array( _
"*Account*", "Approved", "Prepared"), Operator:=xlFilterValues

The "*Account*" code is the one that needs to scan if the table has data that has the text "Account". But in running the code all it filters are the items that are equal to "Approved" and "Prepared" only.

Kindly help! Thank you!

 

[Takae]

You should not be able to use "*" with more than three items in AoutFilter.
If you want to filter morethree items by partial match, you have to explore other methods. It will change depends on next step.
A sample code is follws.(Filtered data will be pasted in sheet2)
Hope this helps.

Sub test()
Dim c, LR As Long
For Each c In Array("*Account*", "Approved", "Prepared")
    LR = Sheets("Sheet2").cells(Rows.count, 1).End(xlUp).Row
    ActiveSheet.Range("$A$1:$B$50000").AutoFilter Field:=1, Criteria1:=c
    Range(Range("A2"), Range("A1").SpecialCells(xlCellTypeLastCell)).Resize(, 2).copy
    Sheets("Sheet2").cells(LR + 1, 1).PasteSpecial Paste:=xlPasteValues
Next
End Sub

 

[Peter_SSs]

Welcome to the MrExcel board!

As you have discovered, and Takae has confirmed, you can't use wildcards in that circumstance.
Here are two alternatives.

1. Used Advanced Filter instead.

Sub AdvFilter()
  Dim aCrit() As Variant
  Dim lngCritCol As Long
  Dim rCrit As Range
  
  aCrit = Array("*Account*", "Approved", "Prepared")  '<- Add your criteria here
  lngCritCol = Cells.Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, SearchFormat:=False).Column + 1
  Cells(1, lngCritCol).Value = Range("A1").Value
  Cells(2, lngCritCol).Resize(UBound(aCrit) - LBound(aCrit) + 1).Value = Application.Transpose(aCrit)
  Set rCrit = Range(Cells(1, lngCritCol), Cells(Rows.Count, lngCritCol).End(xlUp))
  Range("A1", Range("A" & Rows.Count).End(xlUp)).Resize(, 2).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=rCrit, Unique:=False
  Columns(lngCritCol).Delete
End Sub

If using this method, then to re-show any hidden rows you can manually use 'Clear' in the Data ribbon tab (Sort & Filter section) or use this macro

Sub ClearFltr()
  On Error Resume Next
  ActiveSheet.ShowAllData
End Sub

2. You can still use AutoFilter, but with a bit more work, as follows.

Sub AutoFilterWithWildcards()
  Dim RX As Object
  Dim a As Variant
  Dim i As Long
  Dim sCritFull As String
  
  Const sCritShort As String = "*Account*|Approved|Prepared"  '<- Add your criteria here
  
  Set RX = CreateObject("VBScript.RegExp")
  RX.IgnoreCase = True
  RX.Pattern = Replace("^(" & sCritShort & ")$", "*", ".*")
  With Range("A1", Range("A" & Rows.Count).End(xlUp)).Resize(, 2)
    a = .Columns(1).Value
    For i = 2 To UBound(a)
      If RX.Test(a(i, 1)) Then sCritFull = sCritFull & "|" & a(i, 1)
    Next i
    .AutoFilter Field:=1, Criteria1:=Split(Mid(sCritFull, 2), "|"), Operator:=xlFilterValues
  End With
End Sub