AutoFilter last 2 number with wildcard

[HelloKhritty]

Hi,
I'm working on an autofilter in VBA and having a hard time in executing the code. so my problem is, I'd like to get all the number that has the last 2 digits same as on a cell value. for example the cell contains 51. I'd like to get 451, 551, 751,651 all that has the number 51 in it. the code was successful when i am only using the code without the wildcard. but when i am adding the asterisk, the cells are emptied when i clicked the command button. I will appreciate your help a lot. Thank you.

Sub team()
Dim val As String
val = "*" & Range("B3").Value & "*"

With Worksheets("Sheet1").Range("A7")
.AutoFilter Field:=3, Criteria1:=val, Operator:=xlFilterValues
End With

End Sub

 

[DanteAmor]

One way to filter by multiple values is with an array, the values in the array should be stored as text.

Try this:

Sub Filter_Team()
  Dim a() As Variant
  Dim i As Long
  Dim r As Range, f As Range
  Dim cell As String, sTeam As String
  
  Application.ScreenUpdating = False
  If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False
  sTeam = Range("B3").Value
  Set r = Range("C:C")
  Set f = r.Find(sTeam, , xlValues, xlPart, , , False)
  If Not f Is Nothing Then
    cell = f.Address
    Do
      If Right(f.Value, Len(sTeam)) = sTeam Then
        i = i + 1
        ReDim Preserve a(1 To i)
        a(i) = f.Text
      End If
      Set f = r.FindNext(f)
    Loop While Not f Is Nothing And f.Address <> cell
  End If

  If i > 0 Then Range("A7").AutoFilter 3, Array(a), xlFilterValues
  Application.ScreenUpdating = True
End Sub

 

[Peter_SSs]

Could you use Advanced Filter instead?

Place the formula in C3 as shown then run the code below.
(You can use any other cell than C3 provide that the cell immediately above it is blank and you adjust the code to reflect the changed location.)

My original sample data

HelloKhritty.xlsm
	A	B	C	D	E	F	G	H	I	J
1
2
3	Team	46	FALSE
4
5
6
7	Hdr1	Hdr2	TEAM	Hdr4	Hdr5	Hdr6	Hdr7	Hdr8	Hdr9	Hdr10
8	data	data	200	data	data	data	data	data	data	data
9	data	data	246	data	data	data	data	data	data	data
10	data	data	5	data	data	data	data	data	data	data
11	data	data	46	data	data	data	data	data	data	data
12	data	data	2569	data	data	data	data	data	data	data
13	data	data	58	data	data	data	data	data	data	data
14	data	data	946	data	data	data	data	data	data	data
15	data	data	101	data	data	data	data	data	data	data
16	data	data	102	data	data	data	data	data	data	data
17	data	data	103	data	data	data	data	data	data	data
18	data	data	104	data	data	data	data	data	data	data
Sheet1

Cell Formulas
Range	Formula
C3	C3	=RIGHT(C8,2)-B$3=0

Sub FilterTeam()
  Range("A7").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("C2:C3"), Unique:=False
End Sub

My sheet after running the code

HelloKhritty.xlsm
	A	B	C	D	E	F	G	H	I	J
1
2
3	Team	46	FALSE
4
5
6
7	Hdr1	Hdr2	TEAM	Hdr4	Hdr5	Hdr6	Hdr7	Hdr8	Hdr9	Hdr10
9	data	data	246	data	data	data	data	data	data	data
11	data	data	46	data	data	data	data	data	data	data
14	data	data	946	data	data	data	data	data	data	data
19
Sheet1

Cell Formulas
Range	Formula
C3	C3	=RIGHT(C8,2)-B$3=0

 

[HelloKhritty]

Both works well, Thanks you so much. You guys are so great.

 

[Peter_SSs]

Both works well,

Good News! But just to be sure that you are getting what you want ..

You originally said

I'd like to get all the number that has the last 2 digits same as on a cell value. for example the cell contains 51. I'd like to get 451, 551, 751,651

However, the code that you had tried was putting an asterisk before and after the B3 number. If that code had worked, then as well as the numbers listed above, you would also have got 511, 2518 etc


Also, if it happens that no teams at all end with the digits in B3 (if that is even possible) then my code will show no teams whereas Dante's code will show all teams. Either way, is that an issue for you?


If you have gone with the AutoFilter method and are happy with the "all teams" result mentioned above then you may also want to consider this adaptation of it which grabs the relevant values a little more directly/efficiently.

Sub Filter_Team_v2()
  Dim a() As Variant
  Dim i As Long
  Dim r As Range, f As Range
  Dim cell As String, sTeam As String
 
  Application.ScreenUpdating = False
  If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False
  sTeam = "*" & Range("B3").Value
  Set r = Range("C:C")
  Set f = r.Find(sTeam, , xlValues, xlWhole, , , False)
  If Not f Is Nothing Then
    cell = f.Address
    Do
      i = i + 1
      ReDim Preserve a(1 To i)
      a(i) = f.Text
      Set f = r.FindNext(f)
    Loop While f.Address <> cell
  End If
  If i > 0 Then Range("A7").AutoFilter 3, Array(a), xlFilterValues
  Application.ScreenUpdating = True
End Sub

If you have gone with Advanced Filter and are happy with the "no teams" result mentioned above and it is possible that the B3 value could be less or more than 2 digits, then just change the C3 formula to

=RIGHT(C8,LEN(B3))-B$3=0