VBA filter number and string

[Maurizio]

Hi all,
with vba, by inserting the value of 1960 into the inputbox I would like to obtain all the following numerical and textual values filtered:​

- 1960​

- 1960/23
- 2401960​

​

Any idea?​

Tia.​

 

[Wookiee]

Does this work for you?

Sub MaurizioInputFilter()
'Crafted by Wookiee @ MrExcel.com

Dim strFilter      As String
Dim rngFiltration  As Range
Dim wksHome        As Worksheet

If wksHome Is Nothing Then

   Set wksHome = ActiveSheet

End If

Set rngFiltration = wksHome.Range("$A$2:$A$51")

strFilter = VBA.InputBox _
   ("Enter your filter criterion:")
strFilter = "=*" & strFilter & "*"

rngFiltration.AutoFilter _
   Field:=1, _
   Criteria1:=strFilter

End Sub

 

[Maurizio]

Hi, unfortunately your code only filters text strings and not also numbers.

 

[RoryA]

You can't use wildcard filters with numbers. Unless you can store all the data as text, your code will have to loop through all the data and build up an array of values that contain the value you are interested in, then use that array with the Operator:=xlFilterValues option of autofilter.

 

[Maurizio]

You can't use wildcard filters with numbers. Unless you can store all the data as text, your code will have to loop through all the data and build up an array of values that contain the value you are interested in, then use that array with the Operator:=xlFilterValues option of autofilter.

OK, thanks!

 

[Peter_SSs]

Another option may be to use Advanced Filter. Assuming the column to filter on is column A with heading in A1 and column Z free to help it might be something like this.

Sub AdvFltr()
  Dim rCrit As Range
  Dim strFilter As String
  
  strFilter = Application.InputBox("Enter your filter criterion:")
  Set rCrit = Range("Z1:Z2")
  rCrit.Cells(2).Formula = Replace("=Find(""#"", A2)", "#", strFilter)
  Intersect(ActiveSheet.UsedRange, Columns("A")).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=rCrit, Unique:=False
  rCrit.Cells(2).ClearContents
End Sub

To clear the filter and re-show all rows you could used the ribbon menus or run the code again and click OK with the input box blank.

 

[Maurizio]

perfect! thank you very much

 

[Peter_SSs]

You're welcome. Glad to contribute.