Advanced filters - variable dynamic criteria filtering

I

IanPM

Board Regular
Joined
Dec 12, 2013
Messages
53
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Data
[/TD]
[TD]Filter selection
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Jan
[/TD]
[/TR]
[TR]
[TD]Jan
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]NowJanuary
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Feb
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]RatJan
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Febby
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]NowFeb
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Hi
Want to use Advanced filter whereby it filters using part supplied selection entered in the same sheet finding & filtering to all data that include this partial data

Based on example if want to filter by Jan It must filter to display
Jan
NowJanuary
RatJan

If filter selection is feb it must filter to
Feb
Febby
NowFeb

So I looking for dynamic selection option that can filer based on any part of the name that meets that criteria whatever the criteria is. It must not be case sensitive
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Sort by date Sort by votes
You can use Advanced Filter with a formula as criteria.

Something like this

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]
A​
[/TD]
[TD="bgcolor: #DCE6F1"]
B​
[/TD]
[TD="bgcolor: #DCE6F1"]
C​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
1​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
Filter selection​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
2​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
Jan​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
3​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
4​
[/TD]
[TD]
Data​
[/TD]
[TD][/TD]
[TD]
Formula​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
5​
[/TD]
[TD]
Jan​
[/TD]
[TD][/TD]
[TD]
TRUE​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
6​
[/TD]
[TD]
NowJanuary​
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
7​
[/TD]
[TD]
Feb​
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
8​
[/TD]
[TD]
RatJan​
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
9​
[/TD]
[TD]
Febby​
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
10​
[/TD]
[TD]
NowFeb​
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


In C5 insert this formula
=ISNUMBER(SEARCH($C$2,A5))

Select data range (A4:A10) and apply Advanced filter using
Criteria Range:$C$4:$C$5

Hope this helps

M.
 
Upvote 0
Re: Advanced filters_VBA - variable dynamic criteria filtering

I have updated the file with information & got it to work up to a point but the filter only work if the data starts with the criteria. I tried to adapt it to below but could not get it to work. From information below you will be able to see the filter proses for criteria entry & where the dynamic data comes in for the filter. In this example it only filers to everything stating with "AFR" not everything that contains "AFR"

VBA Code used ( A12= Country; h5:k6 = criteria range )

Range("A12").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Range("h5:k6"), Unique:=False


Dynamic values formula ( Cell C6 = Account name )

=IF(C6="","*",C6)


[TABLE="width: 635, align: left"]
<tbody>[TR]
[TD="width: 340, bgcolor: transparent, colspan: 4"]
[TABLE="align: left"]
<tbody>[TR]
[TD="width: 300, bgcolor: transparent"][/TD]
[TD="width: 97, bgcolor: transparent"][/TD]
[TD="width: 39, bgcolor: transparent"][/TD]
[TD="width: 97, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, colspan: 2"][/TD]
[TD="bgcolor: transparent, align: left"]
clip_image002.png
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: left"]
clip_image001.png
[/TD]
[/TR]
</tbody>[/TABLE]



<tbody> [TD="width: 326, bgcolor: transparent"] Summary / Evaluation Overview [/TD]
</tbody>
clip_image003.png
clip_image004.png

[/TD]
[TD="width: 144, bgcolor: transparent, colspan: 2"] Based on this input
[/TD]
[TD="width: 217, bgcolor: transparent, colspan: 4"] Currently it only filter to fields starting with "AFR", ignoring the rest
[/TD]
[TD="width: 146, bgcolor: transparent, colspan: 2"]
[/TD]
[/TR]
[TR]
[TD="width: 76, bgcolor: transparent"][/TD]
[TD="width: 68, bgcolor: transparent"][/TD]
[TD="width: 112, bgcolor: transparent"][/TD]
[TD="width: 85, bgcolor: transparent"][/TD]
[TD="width: 104, bgcolor: transparent"][/TD]
[TD="width: 40, bgcolor: transparent"][/TD]
[TD="width: 38, bgcolor: transparent"][/TD]
[TD="width: 57, bgcolor: transparent"][/TD]
[TD="width: 66, bgcolor: transparent"][/TD]
[TD="width: 57, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 91, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 76, bgcolor: transparent"] Selections
[/TD]
[TD="width: 68, bgcolor: transparent"][/TD]
[TD="width: 112, bgcolor: transparent"][/TD]
[TD="width: 85, bgcolor: transparent"][/TD]
[TD="width: 104, bgcolor: transparent"][/TD]
[TD="width: 78, bgcolor: transparent, colspan: 2"] It must filter to this
[/TD]
[TD="width: 57, bgcolor: transparent"][/TD]
[TD="width: 66, bgcolor: transparent"][/TD]
[TD="width: 57, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 91, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 340, colspan: 4"]
clip_image005.png
Filter options​
[/TD]
[TD="width: 104, bgcolor: transparent"][/TD]
[TD="width: 40, bgcolor: transparent"][/TD]
[TD="width: 38, bgcolor: transparent"][/TD]
[TD="width: 180, colspan: 3"] Criteria - In sheet filter
[/TD]
[TD="width: 64"]
[/TD]
[TD="width: 91, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 76"]
Country​
[/TD]
[TD="width: 68"]
Account #​
[/TD]
[TD="width: 112"] Account name
[/TD]
[TD="width: 85"]
Reference​
[/TD]
[TD="width: 104, bgcolor: transparent"][/TD]
[TD="width: 40, bgcolor: transparent"][/TD]
[TD="width: 38, bgcolor: transparent"][/TD]
[TD="width: 57"]
Country​
[/TD]
[TD="width: 66"]
Account #​
[/TD]
[TD="width: 57"]
Account name​
[/TD]
[TD="width: 64"]
Reference​
[/TD]
[TD="width: 91, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 76"]
[/TD]
[TD="width: 68"]
[/TD]
[TD="width: 112"] AFR
[/TD]
[TD="width: 85"]
[/TD]
[TD="width: 104, bgcolor: transparent"][/TD]
[TD="width: 40, bgcolor: transparent"][/TD]
[TD="width: 38, bgcolor: transparent"][/TD]
[TD="width: 57, bgcolor: transparent"] *
[/TD]
[TD="width: 66, bgcolor: transparent"] *
[/TD]
[TD="width: 57, bgcolor: transparent"] AFR
[/TD]
[TD="width: 64, bgcolor: transparent"] *
[/TD]
[TD="width: 91, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 76, bgcolor: transparent"][/TD]
[TD="width: 68, bgcolor: transparent"][/TD]
[TD="width: 112, bgcolor: transparent"][/TD]
[TD="width: 85, bgcolor: transparent"][/TD]
[TD="width: 104, bgcolor: transparent"][/TD]
[TD="width: 40, bgcolor: transparent"][/TD]
[TD="width: 38, bgcolor: transparent"][/TD]
[TD="width: 57, bgcolor: transparent"][/TD]
[TD="width: 66, bgcolor: transparent"][/TD]
[TD="width: 57, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 91, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 444, colspan: 5"]
Summary selection for filtered information​
[/TD]
[TD="width: 40, bgcolor: transparent"][/TD]
[TD="width: 38, bgcolor: transparent"][/TD]
[TD="width: 57, bgcolor: transparent"][/TD]
[TD="width: 66, bgcolor: transparent"][/TD]
[TD="width: 57, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 91, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 76"]
​
[/TD]
[TD="width: 68"]
​
[/TD]
[TD="width: 112"]
[/TD]
[TD="width: 85"]
​
[/TD]
[TD="width: 104"]
2018​
[/TD]
[TD="width: 40, bgcolor: transparent"][/TD]
[TD="width: 38, bgcolor: transparent"][/TD]
[TD="width: 57, bgcolor: transparent"][/TD]
[TD="width: 66, bgcolor: transparent"][/TD]
[TD="width: 57, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 91, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 76"]
Subtotal​
[/TD]
[TD="width: 68"]
​
[/TD]
[TD="width: 112"]
[/TD]
[TD="width: 85"]
​
[/TD]
[TD="width: 104"]
18,332 ​
[/TD]
[TD="width: 40, bgcolor: transparent"][/TD]
[TD="width: 38, bgcolor: transparent"][/TD]
[TD="width: 57, bgcolor: transparent"][/TD]
[TD="width: 66, bgcolor: transparent"][/TD]
[TD="width: 57, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 91, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 76"]
​
[/TD]
[TD="width: 68"]
​
[/TD]
[TD="width: 112"]
[/TD]
[TD="width: 85"]
​
[/TD]
[TD="width: 104"]
[/TD]
[TD="width: 40, bgcolor: transparent"][/TD]
[TD="width: 38, bgcolor: transparent"][/TD]
[TD="width: 57, bgcolor: transparent"][/TD]
[TD="width: 66, bgcolor: transparent"][/TD]
[TD="width: 57, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 91, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 76"]
Country​
[/TD]
[TD="width: 68"]
Account #​
[/TD]
[TD="width: 112"] Account name
[/TD]
[TD="width: 85"]
Reference​
[/TD]
[TD="width: 104"] Val1
[/TD]
[TD="width: 40, bgcolor: transparent"][/TD]
[TD="width: 38, bgcolor: transparent"][/TD]
[TD="width: 57, bgcolor: transparent"][/TD]
[TD="width: 66, bgcolor: transparent"][/TD]
[TD="width: 57, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 91, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 76"]
ZA​
[/TD]
[TD="width: 68"]
10001​
[/TD]
[TD="width: 112"] African-East

[/TD]
[TD="width: 85"]
AA​
[/TD]
[TD="width: 104"] 500
[/TD]
[TD="width: 40, bgcolor: transparent"][/TD]
[TD="width: 38, bgcolor: transparent"][/TD]
[TD="width: 57, bgcolor: transparent"][/TD]
[TD="width: 66, bgcolor: transparent"][/TD]
[TD="width: 57, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 91, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 76"]
ZA​
[/TD]
[TD="width: 68"]
10001​
[/TD]
[TD="width: 112"] African-East
[/TD]
[TD="width: 85"]
AB​
[/TD]
[TD="width: 104"] 550
[/TD]
[TD="width: 40, bgcolor: transparent"][/TD]
[TD="width: 38, bgcolor: transparent"][/TD]
[TD="width: 57, bgcolor: transparent"][/TD]
[TD="width: 66, bgcolor: transparent"][/TD]
[TD="width: 57, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 91, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 76"]
ZA​
[/TD]
[TD="width: 68"]
10001​
[/TD]
[TD="width: 112"] African-East
[/TD]
[TD="width: 85"]
AC​
[/TD]
[TD="width: 104"] 605
[/TD]
[TD="width: 40, bgcolor: transparent"][/TD]
[TD="width: 38, bgcolor: transparent"][/TD]
[TD="width: 57, bgcolor: transparent"][/TD]
[TD="width: 66, bgcolor: transparent"][/TD]
[TD="width: 57, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 91, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 76"]
ZA​
[/TD]
[TD="width: 68"]
10001​
[/TD]
[TD="width: 112"] African-East
[/TD]
[TD="width: 85"]
AD​
[/TD]
[TD="width: 104"] 666
[/TD]
[TD="width: 40, bgcolor: transparent"][/TD]
[TD="width: 38, bgcolor: transparent"][/TD]
[TD="width: 57, bgcolor: transparent"][/TD]
[TD="width: 66, bgcolor: transparent"][/TD]
[TD="width: 57, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 91, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 76"]
ZA​
[/TD]
[TD="width: 68"]
10002​
[/TD]
[TD="width: 112"] East-Africa
[/TD]
[TD="width: 85"]
AA​
[/TD]
[TD="width: 104"] 600
[/TD]
[TD="width: 40, bgcolor: transparent"][/TD]
[TD="width: 38, bgcolor: transparent"][/TD]
[TD="width: 57, bgcolor: transparent"][/TD]
[TD="width: 66, bgcolor: transparent"][/TD]
[TD="width: 57, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 91, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 76"]
ZA​
[/TD]
[TD="width: 68"]
10002​
[/TD]
[TD="width: 112"] East-Africa
[/TD]
[TD="width: 85"]
AB​
[/TD]
[TD="width: 104"] 660
[/TD]
[TD="width: 40, bgcolor: transparent"][/TD]
[TD="width: 38, bgcolor: transparent"][/TD]
[TD="width: 57, bgcolor: transparent"][/TD]
[TD="width: 66, bgcolor: transparent"][/TD]
[TD="width: 57, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 91, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 76"]
ZA​
[/TD]
[TD="width: 68"]
10002​
[/TD]
[TD="width: 112"] East-Africa
[/TD]
[TD="width: 85"]
AC​
[/TD]
[TD="width: 104"] 726
[/TD]
[TD="width: 40, bgcolor: transparent"][/TD]
[TD="width: 38, bgcolor: transparent"][/TD]
[TD="width: 57, bgcolor: transparent"][/TD]
[TD="width: 66, bgcolor: transparent"][/TD]
[TD="width: 57, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 91, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 76"]
ZA​
[/TD]
[TD="width: 68"]
10002​
[/TD]
[TD="width: 112"] East-Africa
[/TD]
[TD="width: 85"]
AD​
[/TD]
[TD="width: 104"] 799
[/TD]
[TD="width: 40, bgcolor: transparent"][/TD]
[TD="width: 38, bgcolor: transparent"][/TD]
[TD="width: 57, bgcolor: transparent"][/TD]
[TD="width: 66, bgcolor: transparent"][/TD]
[TD="width: 57, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 91, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 76"]
ZA​
[/TD]
[TD="width: 68"]
10003​
[/TD]
[TD="width: 112"] AFR
[/TD]
[TD="width: 85"]
AA​
[/TD]
[TD="width: 104"] 850
[/TD]
[TD="width: 40, bgcolor: transparent"][/TD]
[TD="width: 38, bgcolor: transparent"][/TD]
[TD="width: 57, bgcolor: transparent"][/TD]
[TD="width: 66, bgcolor: transparent"][/TD]
[TD="width: 57, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 91, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 76"]
ZA​
[/TD]
[TD="width: 68"]
10003​
[/TD]
[TD="width: 112"] AFR
[/TD]
[TD="width: 85"]
AB​
[/TD]
[TD="width: 104"] 935
[/TD]
[TD="width: 40, bgcolor: transparent"][/TD]
[TD="width: 38, bgcolor: transparent"][/TD]
[TD="width: 57, bgcolor: transparent"][/TD]
[TD="width: 66, bgcolor: transparent"][/TD]
[TD="width: 57, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 91, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 76"]
ZA​
[/TD]
[TD="width: 68"]
10003​
[/TD]
[TD="width: 112"] AFR
[/TD]
[TD="width: 85"]
AC​
[/TD]
[TD="width: 104"] 1,029
[/TD]
[TD="width: 40, bgcolor: transparent"][/TD]
[TD="width: 38, bgcolor: transparent"][/TD]
[TD="width: 57, bgcolor: transparent"][/TD]
[TD="width: 66, bgcolor: transparent"][/TD]
[TD="width: 57, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 91, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 76"]
ZA​
[/TD]
[TD="width: 68"]
10003​
[/TD]
[TD="width: 112"] AFR
[/TD]
[TD="width: 85"]
AD​
[/TD]
[TD="width: 104"] 1,131
[/TD]
[TD="width: 40, bgcolor: transparent"][/TD]
[TD="width: 38, bgcolor: transparent"][/TD]
[TD="width: 57, bgcolor: transparent"][/TD]
[TD="width: 66, bgcolor: transparent"][/TD]
[TD="width: 57, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 91, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 76"]
ZA​
[/TD]
[TD="width: 68"]
10004​
[/TD]
[TD="width: 112"] WEST
[/TD]
[TD="width: 85"]
AA​
[/TD]
[TD="width: 104"] 900
[/TD]
[TD="width: 40, bgcolor: transparent"][/TD]
[TD="width: 38, bgcolor: transparent"][/TD]
[TD="width: 57, bgcolor: transparent"][/TD]
[TD="width: 66, bgcolor: transparent"][/TD]
[TD="width: 57, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 91, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 76"]
ZA​
[/TD]
[TD="width: 68"]
10004​
[/TD]
[TD="width: 112"] WEST
[/TD]
[TD="width: 85"]
AB​
[/TD]
[TD="width: 104"] 990
[/TD]
[TD="width: 40, bgcolor: transparent"][/TD]
[TD="width: 38, bgcolor: transparent"][/TD]
[TD="width: 57, bgcolor: transparent"][/TD]
[TD="width: 66, bgcolor: transparent"][/TD]
[TD="width: 57, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 91, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 76"]
ZA​
[/TD]
[TD="width: 68"]
10004​
[/TD]
[TD="width: 112"] WEST
[/TD]
[TD="width: 85"]
AC​
[/TD]
[TD="width: 104"] 1,089
[/TD]
[TD="width: 40, bgcolor: transparent"][/TD]
[TD="width: 38, bgcolor: transparent"][/TD]
[TD="width: 57, bgcolor: transparent"][/TD]
[TD="width: 66, bgcolor: transparent"][/TD]
[TD="width: 57, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 91, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 76"]
ZA​
[/TD]
[TD="width: 68"]
10004​
[/TD]
[TD="width: 112"] WEST
[/TD]
[TD="width: 85"]
AD​
[/TD]
[TD="width: 104"] 1,198
[/TD]
[TD="width: 40, bgcolor: transparent"][/TD]
[TD="width: 38, bgcolor: transparent"][/TD]
[TD="width: 57, bgcolor: transparent"][/TD]
[TD="width: 66, bgcolor: transparent"][/TD]
[TD="width: 57, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 91, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 76"]
ZA​
[/TD]
[TD="width: 68"]
10005​
[/TD]
[TD="width: 112"] WEST -1
[/TD]
[TD="width: 85"]
AA​
[/TD]
[TD="width: 104"] 1,100
[/TD]
[TD="width: 40, bgcolor: transparent"][/TD]
[TD="width: 38, bgcolor: transparent"][/TD]
[TD="width: 57, bgcolor: transparent"][/TD]
[TD="width: 66, bgcolor: transparent"][/TD]
[TD="width: 57, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 91, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 76"]
ZA​
[/TD]
[TD="width: 68"]
10005​
[/TD]
[TD="width: 112"] WEST -1
[/TD]
[TD="width: 85"]
AB​
[/TD]
[TD="width: 104"] 1,210
[/TD]
[TD="width: 40, bgcolor: transparent"][/TD]
[TD="width: 38, bgcolor: transparent"][/TD]
[TD="width: 57, bgcolor: transparent"][/TD]
[TD="width: 66, bgcolor: transparent"][/TD]
[TD="width: 57, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 91, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 76"]
ZA​
[/TD]
[TD="width: 68"]
10005​
[/TD]
[TD="width: 112"] WEST -1
[/TD]
[TD="width: 85"]
AC​
[/TD]
[TD="width: 104"] 1,331
[/TD]
[TD="width: 40, bgcolor: transparent"][/TD]
[TD="width: 38, bgcolor: transparent"][/TD]
[TD="width: 57, bgcolor: transparent"][/TD]
[TD="width: 66, bgcolor: transparent"][/TD]
[TD="width: 57, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 91, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 76"]
ZA​
[/TD]
[TD="width: 68"]
10005​
[/TD]
[TD="width: 112"] WEST -1
[/TD]
[TD="width: 85"]
AD​
[/TD]
[TD="width: 104"] 1,464
[/TD]
[TD="width: 40, bgcolor: transparent"][/TD]
[TD="width: 38, bgcolor: transparent"][/TD]
[TD="width: 57, bgcolor: transparent"][/TD]
[TD="width: 66, bgcolor: transparent"][/TD]
[TD="width: 57, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 91, bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Re: Advanced filters_VBA - variable dynamic criteria filtering

Try this formula in J6
=IF(C6="","*","*"&C6&"*")

So you see in H5:K6 (Criteria Range)

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]
H​
[/TD]
[TD="bgcolor: #DCE6F1"]
I​
[/TD]
[TD="bgcolor: #DCE6F1"]
J​
[/TD]
[TD="bgcolor: #DCE6F1"]
K​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
5​
[/TD]
[TD]
Country​
[/TD]
[TD]
Account #​
[/TD]
[TD]
Account name​
[/TD]
[TD]
Reference​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
6​
[/TD]
[TD]
*​
[/TD]
[TD]
*​
[/TD]
[TD]
*AFR*​
[/TD]
[TD]
*​
[/TD]
[/TR]
</tbody>[/TABLE]


Hope this helps

M.
 
Upvote 0
You must log in or register to reply here.

Similar threads

Z
  • Solved
VBA: Advanced Filter Limitations
Replies
3
Views
832
zero269
Z
E
  • Solved
FILTER Formula: Simultaneous AND and OR Conditions?
2
Replies
10
Views
958
Peter_SSs
Peter_SSs
S
  • Question Question
Return the Sum of a set of Data based on multiple dynamic criteria
Replies
1
Views
394
sopphiron
S
A
  • Question Question
Dynamically add new rows via VBA when using IFERROR TEXTJOIN and FILTER formulas from Table?
Replies
0
Views
538
AlfredDB
A
B
  • Question Question
VBA code to cut and paste multiple filtered rows based on multiple criteria
Replies
2
Views
1K
Alex Blakenburg
A

Forum statistics

Threads
1,224,818
Messages
6,181,152
Members
453,021
Latest member
Justyna P

Share this page

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