VBA filter number and string

Maurizio

Well-known Member
Joined
Oct 15, 2002
Messages
695
Office Version
  1. 2007
Platform
  1. Windows
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.​
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Does this work for you?

VBA Code:
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
 
Upvote 0
Hi, unfortunately your code only filters text strings and not also numbers.
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0
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.

VBA Code:
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.
 
Upvote 0

Forum statistics

Threads
1,226,695
Messages
6,192,478
Members
453,727
Latest member
tuong_ng89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top