Dynamic Named Range as Array for AutoFilter not working

[shella]

Hey All! I've been searching the forums in vain and am hoping someone out there can help. I have a dynamic named range ("FilterCriteria") and am trying to use it as an array for autofilter. I'd like to see if any of the "FilterCriteria" values appear in each cell in Column H. H is a helper column that is concatenating multiple other columns, as my filter variables exist in several different columns. I can't seem to get the below code to work, and imagine there might even be a better way?

Thank you so much!

Sub RunFilter_New()
Dim vCrit As Variant


'Need Help: Trying to see if any value in my "FilterCriteria" named range
'appears in Column H on "Data" worksheet (by row)
'I'm using H as a helper column since my filter criteria span multiple columns (A, E, F, G)
'If it appears in the H cell - in any part of the cell, autofilter those rows
'If the value does not appear in the H cell, hide that row


vCrit = Worksheets("KEEP-unique").Range("FilterCriteria").Value


Worksheets("Data").Range("$A$4").CurrentRegion.AutoFilter _
    Field:=8, _
    Criteria1:="*" & Application.Transpose(vCrit) & "*", _
    Operator:=xlFilterValues


End Sub

 

[Peter_SSs]

Re: Dynamic Named Range as Array for AutoFilter not working - please HELP!

Peter,

You, sir, are a gentleman and a scholar! Thank you 1000x over ..

You're welcome and thanks for your kind words.

.. why that autofilter line works ..

Just to clarify, my code uses 'AdvancedFilter' which is different to 'AutoFilter'.
There are a number of different ways to use Advanced Filter but a simpler example of what we have done would be as below. Suppose we wanted to filter the data and show rows that have "a" in column A or have "c" in column C. The result would be to show rows 3, 5 & 6
To do this we would put the formula shown in E2. It returns FALSE so that row will not be shown when filtered. We don't copy this formula down, but if we did, it would return TRUE for rows 3, 5 & 6 and FALSE for the other rows.
When Advanced filter is run with criteria range E1:E2 (E1 must be empty) it effectively applies the E2 formula as if it was copied down all the rows and shows those where TRUE would have been returned.
Note that the formula I used in your problem doesn't return TRUE/FALSE but either a positive number (counts as TRUE) or an error (counts as FALSE)

Book1
	A	B	C	D	E
1	Hdr 1	Hdr 2	Hdr 3
2	w	data	x		FALSE
3	a	data	y
4	c	data	w
5	a	data	a
6	b	data	c
Sheet1

Because I like to learn about the actual code .. could you perhaps .. point me in the right direction .. to read up on it/learn more about it?

One good place to start would be https://www.contextures.com/xladvfilter01.html

Also, is there a way I can/should mark this post {SOLVED} ?

Just replying stating that you are satisfied, as you have, is all that is required. We don't like to 'close off' solved threads as it is not uncommon for somebody else to later come along and provide an even better solution, and we like to encourage that.

 

[shella]

Re: Dynamic Named Range as Array for AutoFilter not working - please HELP!

Peter,

Thanks for the explanation - that made it very clear! The way that I'm understanding it, the code works as an "OR" statement (if row contains "Criteria1" OR "Criteria2" it is visible. I read through the link you provided and tried to do some research to back into this answer myself, but can't seem to get on the right path. Do you know how I change the code you provided below from an OR to and AND statement so that, in order for the row to remain visible, it must contain ALL of the criteria in the array? I inserted my understanding of how your two original code lines work, based on our earlier conversation, but please correct me if my thinking is wrong there. I"m assuming one of the objects in your far right () is what should be changed to force an AND match, but i couldn't research my way into identifying what piece of the code to alter to get it to work properly. Thanks again for your brainpower!

'>>>>My understanding is this would return "APPLE" but not "Pineapple"
rCrit.Cells(2).Formula = "=LOOKUP(9.99E+307,SEARCH(FilterCriteria,TEXTJOIN("" "",TRUE,A4,E4:AB4)))"
'>>>>My understanding is that this returns both "Apple" and "Pineapple" allowing for a wildcard
'rCrit.Cells(2).Formula = "=LOOKUP(9.99E+307,SEARCH(""|""&FilterCriteria&""|"",""|""&TEXTJOIN(""|"",TRUE,A4,E4:AB4)&""|""))"

Best,
Shella

 

[Peter_SSs]

Re: Dynamic Named Range as Array for AutoFilter not working - please HELP!

Do you know how I change the code you provided below from an OR to and AND statement so that, in order for the row to remain visible, it must contain ALL of the criteria in the array?

That is a very different goal to what was originally asked and does not seem to relate to the original data as no rows would meet that.
Are we still looking in multiple columns or just one column now?

Could we have a new small set of dummy data, criteria and expected results?

 

[shella]

Re: Dynamic Named Range as Array for AutoFilter not working - please HELP!

Could we have a new small set of dummy data, criteria and expected results?

Peter,

Here is a small set with criteria, I've highlighted rows 7 and 10 which would be the expected advanced filter result. Thank you SO very much!

Excel 2016 (Windows) 32 bit
[TABLE="class: head"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[TH][/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]A[/COLOR]​

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]B[/COLOR]​

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]C[/COLOR]​

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]D[/COLOR]​

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]E[/COLOR]​

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]F[/COLOR]​

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]G[/COLOR]​

[/TH]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]1[/COLOR]​

[/TD]
[TD="bgcolor: #003399"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Source[/COLOR][/TD]
[TD="bgcolor: #003399"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Tag1[/COLOR][/TD]
[TD="bgcolor: #003399"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Tag 2[/COLOR][/TD]
[TD="bgcolor: #003399"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Tag 3[/COLOR][/TD]
[TD="bgcolor: #003399"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Prop Area 1[/COLOR][/TD]
[TD][/TD]
[TD="bgcolor: #70AD47"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Filter Criteria[/COLOR][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]2[/COLOR]​

[/TD]
[TD]Customer Engagement[/TD]
[TD]Risk/Concern[/TD]
[TD][/TD]
[TD][/TD]
[TD]All[/TD]
[TD][/TD]
[TD]Black Hat[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]3[/COLOR]​

[/TD]
[TD]Competitive Intel[/TD]
[TD]Hot Button - Hope[/TD]
[TD][/TD]
[TD][/TD]
[TD]Management[/TD]
[TD][/TD]
[TD]Kudo[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]4[/COLOR]​

[/TD]
[TD]Black Hat[/TD]
[TD]Hot Button - Fear[/TD]
[TD]Metric[/TD]
[TD][/TD]
[TD]Technical[/TD]
[TD][/TD]
[TD]Management[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]5[/COLOR]​

[/TD]
[TD]Customer Engagement[/TD]
[TD]Ghost[/TD]
[TD][/TD]
[TD][/TD]
[TD]Transition[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]6[/COLOR]​

[/TD]
[TD]Competitive Intel[/TD]
[TD]Defend[/TD]
[TD][/TD]
[TD][/TD]
[TD]Cost/BOES[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]7[/COLOR]​

[/TD]
[TD="bgcolor: #FFF2CC"]Black Hat[/TD]
[TD="bgcolor: #FFF2CC"]Kudo[/TD]
[TD="bgcolor: #FFF2CC"]Hot Button - Hope[/TD]
[TD="bgcolor: #FFF2CC"][/TD]
[TD="bgcolor: #FFF2CC"]Management[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]8[/COLOR]​

[/TD]
[TD]Industry Day[/TD]
[TD]Metric[/TD]
[TD][/TD]
[TD][/TD]
[TD]Past Performance[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]9[/COLOR]​

[/TD]
[TD]SME Interview[/TD]
[TD]Proof[/TD]
[TD][/TD]
[TD][/TD]
[TD]Contracts & Subcontracts[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]10[/COLOR]​

[/TD]
[TD="bgcolor: #FFF2CC"]Black Hat[/TD]
[TD="bgcolor: #FFF2CC"]Kudo[/TD]
[TD="bgcolor: #FFF2CC"][/TD]
[TD="bgcolor: #FFF2CC"][/TD]
[TD="bgcolor: #FFF2CC"]Management[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]11[/COLOR]​

[/TD]
[TD]Solutioning[/TD]
[TD]Past Performance[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]12[/COLOR]​

[/TD]
[TD]Other[/TD]
[TD]Discriminator[/TD]
[TD][/TD]
[TD]Ghost[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: Sheet1[/TD]
[/TR]
</tbody>[/TABLE]
Thanks again!

Best,
Shella

 

[shella]

Re: Dynamic Named Range as Array for AutoFilter not working - please HELP!

Peter,

Apologies for the odd code - it doesn't appear that way in the preview post and I can't seem to find out how to fix. I used the forum tools add-in suggested in the FAQ...

Hopefully you can see past the sloppiness! Thank you!

Best,
Shella

 

[Peter_SSs]

Re: Dynamic Named Range as Array for AutoFilter not working - please HELP!

It appears you are not using the latest version of Forum Tools as the latest version does not use those #FFFFFF type numbers for colours. Follow the 'Look here' link in my signature block below to track to the latest version.

I'm not exactly sure what columns I am looking at but are we only looking for Black Hat in column A, Kudo in column B and Management in column E?
Or can anything be in any column more like before?

 

[shella]

Re: Dynamic Named Range as Array for AutoFilter not working - please HELP!

I'm not exactly sure what columns I am looking at but are we only looking for Black Hat in column A, Kudo in column B and Management in column E?
Or can anything be in any column more like before?

Peter,

The filter criteria will only exist in Columns A / B / E. I'll make sure to download the newer version of the Forum Tools - thank you!

Best,
Shella

 

[Peter_SSs]

Re: Dynamic Named Range as Array for AutoFilter not working - please HELP!

The filter criteria will only exist in Columns A / B / E.

That still doesn't fully answer my question

1. Could Black Hat be in col E, Kudo in col A and Management in col B?

2. Could Kudo & Management be in the same cell in col B and Black Hat in col E?

3. Or do the 3 items in the Criteria list appear in col A, B & E respectively (if they occur at all)?

4. Will there always be exactly 3 items in this Criteria List? If not could we have another example to demonstrate how it would work with, say 5 (or 2) items in the list?

 

[shella]

Re: Dynamic Named Range as Array for AutoFilter not working - please HELP!

Peter,

Apologies - please see answers below. Thank you!

1. Could Black Hat be in col E, Kudo in col A and Management in col B? No sir - each criteria will only appear in the columns they are currently shown in on the dummy dataset. Those are set.

2. Could Kudo & Management be in the same cell in col B and Black Hat in col E? No sir

3. Or do the 3 items in the Criteria list appear in col A, B & E respectively (if they occur at all)? Exactly! I should've specified this - thank you

4. Will there always be exactly 3 items in this Criteria List? If not could we have another example to demonstrate how it would work with, say 5 (or 2) items in the list? There will be many more than just 3 criteria in the real dataset, is it possible that I can use your code as a starting point and modify to reflect this? Or does this require a complete re-write of the code? I anticipate the full dataset will have 50+ possible criteria spread across Columns (A, E:AD) right now...

 

[Peter_SSs]

Re: Dynamic Named Range as Array for AutoFilter not working - please HELP!

4. ...If not could we have another example to demonstrate how it would work with, say 5 (or 2) items in the list?

Unfortunately didn't get this so I'll have to guess.

Is it correct that the only visible rows after filtering the data below how you envisage would be rows 2, 5 & 6?
Or would row 7 also be visible?

Book1
	A	B	C	D	E	F	G
1	Hdr 1	Hdr 2	Hdr 3	Hdr 4	Hdr 5		Criteria
2	a	d	x		e		a
3	b	c					b
4	a	w	x	g	e		c
5	b	c		g	e		d
6	a	c	p		e		e
7	xa	c	p	g	e
8	v	w	x	y	z
Sheet1