Advance Filtering in VBA with multiple wildcard criterias

[Tacty]

Hello everyone. working on a sheet that i would like to use some advance autofilters on with multiple wild cards.
basically when I click the program button it will filter out with wild cards on a specific column.

would like it to filter for

*Honda-CFW-6
*Honda-CFW-8
*Honda-CFW-3
*Honda-CFW-2
*Honda-CFW-1

though they will have more information following after, I would just like to filter for anything "containing" those following texts, even though they will have more information after it. Such as Honda-CFW-6122-555.

i have included a sample workbook, if anyone could show me how to get this started i would be greatful.

 

[DanteAmor]

Hi and welcome to MrExcel!

Try this:

In cell N1 you must put the same header that you have in cell J3.
From cell N2 and down put the values you want to filter.

Dante Amor
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1														COLUMN10
2														Honda-CFW-6
3	COLUMN1	COLUMN2	COLUMN3	COLUMN4	COLUMN5	COLUMN6	COLUMN7	COLUMN8	COLUMN9	COLUMN10	COLUMN11	COLUMN12		Honda-CFW-8
4										Honda-CFW-1327				Honda-CFW-3
5										Honda-CFW-1333				Honda-CFW-2
6										Honda-CFW-1375				Honda-CFW-1
7										Honda-CFW-2322
8										Honda-CFW-2324
13										Honda-CFW-6313
14										Honda-CFW-6364
15										Honda-CFW-6348
16										Honda-CFW-8339
17										Honda-CFW-8323
18										Honda-CFW-8397
24
Hoja3

Run this macro:

Sub Filtering()
  Range("Tabla1[[#All],[COLUMN10]]").AdvancedFilter xlFilterInPlace, Range("N1:N6"), True
End Sub

------------
NOTE XL2BB:
For the future, it would help greatly if you could give us the sample data in a form that we can copy to test with, rather that a picture.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in
Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Tacty]

Hello Dante, ty for helping.

I have copied the minisheet below, hope it helps more with this example. I have tried your code with the command button, but that did not work. Ran the code and it gave me a 400 error.

but basically, trying to filter multiple selections just using

for example Honda-CFW-6
Honda-CFW-8
Honda-CFW-3
Honda-CFW-2

and so on I Have a lot to filter for, but because it contains those criteria in the beginning it will still show up on the column.

The real numbers would contain the whole thing such as

Honda-CFW-648-133 Honda-CFW-649-134 Honda-CFW-649-136 Honda-CFW-662-137

Same for the Other numbers because they would contain

Honda-CFW-8

and so on.

Sample Excel.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1														Column10
2														Honda-CFW-344-115
3	Column1	Column2	Column3	Column4	Column5	Column6	Column7	Column8	Column9	Column10	Column11	Column12		Honda-CFW-4
4										Honda-CFW-344-115				Honda-CFW-8
5										Honda-CFW-354-116				Honda-CFW-9
6										Honda-CFW-344-117
7										Honda-CFW-344-118
8										Honda-CFW-346-119
9										Honda-CFW-346-120
10										Honda-CFW-446-121
11										Honda-CFW-446-122
12										Honda-CFW-444-123
13										Honda-CFW-444-124
14										Honda-CFW-344-125
15										Honda-CFW-347-126
16										Honda-CFW-347-127
17										Honda-CFW-547-128
18										Honda-CFW-548-129
19										Honda-CFW-548-130
20										Honda-CFW-348-131
21										Honda-CFW-348-132
22										Honda-CFW-648-133
23										Honda-CFW-649-134
24										Honda-CFW-349-135
25										Honda-CFW-649-136
26										Honda-CFW-662-137
27										Honda-CFW-362-138
28										Honda-CFW-861-139
29										Honda-CFW-864-140
30										Honda-CFW-867-141
31										Honda-CFW-862-142
32										Honda-CFW-862-143
33										Honda-CFW-362-144
34										Honda-CFW-962-145
35										Honda-CFW-962-146
36										Honda-CFW-362-147
37										Honda-CFW-362-148
38										Honda-CFW-262-149
39										Honda-CFW-262-150
40										Honda-CFW-281-151
41										Honda-CFW-382-152
42										Honda-CFW-362-153
43										Honda-CFW-362-154
44										Honda-CFW-162-155
45										Honda-CFW-179-156
46										Honda-CFW-177-157
47										Honda-CFW-362-158
Sheet1

 

[DanteAmor]

Try this:

Sub Filtering()
  Range("A3:L" & Range("J" & Rows.Count).End(3).Row).AdvancedFilter _
    xlFilterInPlace, Range("N1", Range("N" & Rows.Count).End(3)), True
End Sub

 

[Tacty]

Hello Dante,

ty fo the help. it does work. is it possible to keep the headers in the colunms?

also please help me to add your code to a command button, i have tried this but it gives me an error

Private Sub CommandButton1_Click()
Sub Filtering()
  Range("A3:L" & Range("J" & Rows.Count).End(3).Row).AdvancedFilter _
    xlFilterInPlace, Range("N1", Range("N" & Rows.Count).End(3)), True
End Sub

 

[Tacty]

sorry for posting again would anyone else be able to help me with the last issues on this code?

also anyway to get the code to sort numerically such as 1,2,3 and letters A,B,C

instead of the data showing up likke

Honda-CFW-3
Honda-CFW-6
Honda-CFW-1

need it to show up as

Honda-CFW-1
Honda-CFW-3
Honda-CFW-6

*and the ability to work with a command button?

Private Sub CommandButton1_Click()
Sub Filtering()
  Range("A3:L" & Range("J" & Rows.Count).End(3).Row).AdvancedFilter _
    xlFilterInPlace, Range("N1", Range("N" & Rows.Count).End(3)), True
End Sub

 

[Alex Blakenburg]

For the sort you would have to sort the whole data table not just the filtered data.

For the Command Button remove the second line "Sub Filtering()"

Private Sub CommandButton1_Click()
  Range("A3:L" & Range("J" & Rows.Count).End(3).Row).AdvancedFilter _
          xlFilterInPlace, Range("N1", Range("N" & Rows.Count).End(3)), True
End Sub

 

[DanteAmor]

is it possible to keep the headers in the colunms?

You can only keep one filter.
The filter I am using in the macro is advanced filter. If you want to keep the "arrow" of the autofilter. Then use the following.

Sort the data, filter, hold the autofilter arrow and it has the name of a CommandButton1_Click:

Sub CommandButton1_Click()
  Dim a As Variant, b As Variant, arr() As Variant
  Dim i As Long, j As Long, k As Long, lr As Long
  
  If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False
  lr = Range("J" & Rows.Count).End(3).Row
  Range("A3:L" & lr).Sort Range("A4"), xlAscending, Header:=xlYes
  a = Range("A4:L" & lr).Value
  b = Range("N1", Range("N" & Rows.Count).End(3)).Value
  
  For i = 1 To UBound(a, 1)
    For j = 1 To UBound(b, 1)
      If a(i, 10) Like b(j, 1) & "*" Then
        k = k + 1
        ReDim Preserve arr(1 To k)
        arr(k) = a(i, 10)
      End If
    Next
  Next
  If k > 0 Then Range("A3:L" & lr).AutoFilter 10, Array(arr), xlFilterValues
End Sub

 

[Tacty]

Hello Dante, the sample sheet seems to work just fine. but on my other sheet the code fails to filter.

would you be able to give me some comments on what the lines do? for example this is for?

Range("A3:L" & lr).Sort Range("A4"), xlAscending, Header:=xlYes

and this?

a = Range("A4:L" & lr).Value

 

[DanteAmor]

For it to work, the data must be in the same positions that you put in your example in post #3

If it doesn't work, you should put the data you're testing with here so I can review it.

Range("A3:L" & lr).Sort Range("A4"), xlAscending, Header:=xlYes

Sort the data

a = Range("A4:L" & lr).Value

Put all data into array 'a'