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

 

[shella]

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

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

Apologies, updated dummy dataset provided below.

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?

	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

<tbody>
</tbody>

Sheet1​

None of the rows would be visible in your table, only if ALL criteria exist in the row would the row be visible. Thank you!

Excel 2016 (Windows) 32 bit

	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]A[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]B[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]C[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]D[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]E[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]F[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]1[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]1[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]2[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]3[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]8[/COLOR]​		[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Criteria[/COLOR]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]2[/COLOR]​	Blue	Seven	Bird	Tree		Blue
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]3[/COLOR]​	Red	Two	Cow	Rose		One
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]4[/COLOR]​	Green	Four	Dog	Grass		Bird
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]5[/COLOR]​	Blue	One	Bird	Tree		Tree
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]6[/COLOR]​	White	Eight	Dog	Grass
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]7[/COLOR]​	Blue	One	Dog	Tree
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]8[/COLOR]​	Red	Six	Cow	Rose

<tbody>
</tbody>

Sheet: Sheet1

<tbody>
</tbody>

Thank you very much!

Best,
Shella

 

[Peter_SSs]

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

None of the rows would be visible in your table, only if ALL criteria exist in the row would the row be visible. Thank you!

Excel 2016 (Windows) 32 bit

	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]A[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]B[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]C[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]D[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]E[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]F[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]1[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]1[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]2[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]3[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]8[/COLOR]​		[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Criteria[/COLOR]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]2[/COLOR]​	Blue	Seven	Bird	Tree		Blue
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]3[/COLOR]​	Red	Two	Cow	Rose		One
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]4[/COLOR]​	Green	Four	Dog	Grass		Bird
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]5[/COLOR]​	Blue	One	Bird	Tree		Tree
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]6[/COLOR]​	White	Eight	Dog	Grass
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]7[/COLOR]​	Blue	One	Dog	Tree
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]8[/COLOR]​	Red	Six	Cow	Rose

<tbody>
</tbody>

Sheet: Sheet1

<tbody>
</tbody>

Thank you very much!

Best,
Shella

I can't keep up with the apparent changes.

The above red quote and the above sample (where you haven't specifically said which rows would be visible, but I'm assuming just row 5) appear to be at odds with the post below. Above, every column has to have an item in the criteria. below they do not.

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

	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]A[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]B[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]C[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]D[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]E[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]F[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]G[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]1[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Source[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Tag1[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Tag 2[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Tag 3[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Prop Area 1[/COLOR]		[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Filter Criteria[/COLOR]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]2[/COLOR]​	Customer Engagement	Risk/Concern			All		Black Hat
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]3[/COLOR]​	Competitive Intel	Hot Button - Hope			Management		Kudo
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]4[/COLOR]​	Black Hat	Hot Button - Fear	Metric		Technical		Management
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]5[/COLOR]​	Customer Engagement	Ghost			Transition
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]6[/COLOR]​	Competitive Intel	Defend			Cost/BOES
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]7[/COLOR]​	Black Hat	Kudo	Hot Button - Hope		Management
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]8[/COLOR]​	Industry Day	Metric			Past Performance
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]9[/COLOR]​	SME Interview	Proof			Contracts & Subcontracts
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]10[/COLOR]​	Black Hat	Kudo			Management
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]11[/COLOR]​	Solutioning	Past Performance
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]12[/COLOR]​	Other	Discriminator		Ghost

<tbody>
</tbody>

Sheet: Sheet1

<tbody>
</tbody>

Thanks again!

Best,
Shella

You stated earlier (post 19) that your real data could have 50+ criteria spread across columns to AD. What is spread across to AD?
- If Criteria is spread across columns then the samples given so far do not reflect that as none have any multiple columns for criteria.
- If data is spread across to AD and you have 50+ criteria, that would mean more than one item in the criteria list for a particular column. In turn, that would mean it impossible for any row to contain all the criteria. That is partly why I asked Q4 in post 18 and why I constructed the sample in post 20 the way I did.

Upshot is - I'm confused.

I'll make sure to download the newer version of the Forum Tools

 

[shella]

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

Peter,

It seems I've unintentionally complicated things again here, so let me try to clear it up a bit The problem arose when crafting the dummy set that worked for the purpose of generating this first code (below), where I wanted to filter a table so that rows remained visible if any of their cells contained ANY criteria in an array I called "filtercriteria."

rCrit.Cells(2).Formula = "=LOOKUP(9.99E+307,SEARCH(FILTERCRITERIA,TEXTJOIN("" "",TRUE,A4:AU4)))"
  Range("A3").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=rCrit, UNIQUE:=False  rCrit.ClearContents

When we accomplished that (thank you!), I wondered if it was possible (and naively thought it would be a very minor tweak to that same code), to filter the table so that rows remained visible only if ALL of the criteria in the "filteriacriteria" array existed anywhere in that row's cells. My original dummy dataset was not built to test this, so I created the new one (from my last post) to try to demonstrate. And you correctly assumed that my intent was that only row 5 would be visible.

I hope this explains the discrepancies between the two dummy data sets and clarifies what I'm trying to achieve with this second piece of code. Summaey:

• not every column in the row must be populated, nor contain a criteria
• but EVERY word in the criteria list must appear SOMEWHERE in the row in order for that row to remain visible (ex row 5 in that last table)
• the criteria can be in any combination, in any cell, and a cell can have multiple criteria...i really want the code to verify the existence of the criteria words in the row...if 100% of the words are present, the row remains visible
• the criteria are housed in a single column array - dynamic named range called "filtercriteria"

I hope this straightens things out a bit. Standing by for any additional questions and appreciate your patience with me!

Thanks,
Shella

 

[Peter_SSs]

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

Yes, that is clearer, thanks.
This is my sample data, nothing else on the sheet. filterCriteria is the range H2:H5

Book1
	A	B	C	D	E	F	G	H
1								Criteria
2								Blue
3	Hdr 1	Hdr 2	Hdr 3	Hdr 4				One
4	Blue	Seven	Bird	Tree				Bird
5	Red	Two	Cow	Rose				Tree
6	Green	Four	Dog	Grass
7	Blue	One	Bird	Tree
8	White	Eight	Dog	Grass
9	Blue	One	Dog	Tree
10	Red	Six	Cow	Rose
11
Sheet1

Basically same code as before but with a different formula again.

Sub Adv_Filter()
  Dim rCrit As Range
  
  Set rCrit = Range("Y1:Y2")
  
  rCrit.Cells(2).Formula = "=SUMPRODUCT(--ISNUMBER(SEARCH("" ""&filterCriteria&"" "","" ""&A4:D4&"" "")))=COUNTA(filterCriteria)"

  Range("A3").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=rCrit, Unique:=False
  rCrit.ClearContents
End Sub

After the code:

Book1
	A	B	C	D	E
1
2
3	Hdr 1	Hdr 2	Hdr 3	Hdr 4
7	Blue	One	Bird	Tree
11
Sheet1

 

[shella]

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

Peter,

Thanks again! I couldn't get the code to work for me...wondering if blank cells caused the issue? I tested it with the FilterCriteria array consisting of only one item, which should have left a few rows of my data visible, but all rows were hidden. I was wondering if you thought it was a single criteria issue or a blank cells issue? Trying to brainstorm a workaround and looking forward to your thoughts!

Thanks,
Shella

 

[Peter_SSs]

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

I was wondering if you thought it was a single criteria issue or a blank cells issue?

I think neither. In the rCrit formula part, I didn't allow for differing numbers of columns in Range("A3").CurrentRegion
The formula I used was for the specific example that had 4 columns - my mistake, sorry.

Try this instead. You also need to be sure that Range("A3").CurrentRegion is addressing the correct region to filter.

Sub Adv_Filter_v2()
  Dim rCrit As Range
  
  Set rCrit = Range("Y1:Y2")
  With Range("A3").CurrentRegion
    rCrit.Cells(2).Formula = "=SUMPRODUCT(--ISNUMBER(SEARCH("" ""&filterCriteria&"" "","" ""&" & .Rows(2).Address(0, 0) & "&"" "")))=COUNTA(filterCriteria)"
    .AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=rCrit, Unique:=False
  End With
  rCrit.ClearContents
End Sub

 

[shella]

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

Peter,

Thanks again! Unfortunately I couldn't get this code to return any visible rows no matter the FilterCriteria. I'm certain the error is on my end, and there is a nuance/difference between the dummy dataset and my actual file that is the issue...but i'm not sure how to identify what that may be. Any suggestions?

Thanks for your continued assistance with this!

Best,
Shella

 

[Peter_SSs]

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

Can you make up a file with a small set of your data where the code doesn't work, disguise any sensitive data (perhaps a few Find/Replaces) & upload it to a file-share site (eg DroBox) and provide a shared link to the file?

 

[shella]

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

Can you make up a file with a small set of your data where the code doesn't work, disguise any sensitive data (perhaps a few Find/Replaces) & upload it to a file-share site (eg DroBox) and provide a shared link to the file?

Done! I've created buttons for the filters on the spreadsheet for quick/easy access. The Filter OR works brilliantly and, as you'll see, I just can't get Filter AND (v1 or v2) to return expected results. Expected results are the two rows highlighted in yellow. Thank you so much!

https://www.dropbox.com/s/rz78ilcrmwmornb/MrExcel.xlsm?dl=0

Best,
Shella

 

[Peter_SSs]

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

Your named range filterCriteria is not correct.
If you look back to post 24 for example, I specified that filterCriteria was H2:H5, not H1:H5
Your definition of filterCriteria in that sample file results in the range A1:A5. Therefore it contains the word "FilterCriteria" (in A1). Since that word is not found in any of the data table rows, they all get hidden, quite correctly.

The definition of filterCriteria needs to be
=OFFSET(FilterCriteria!$A$1,1,0,COUNTA(FilterCriteria!$A:$A)-1,1)

Both 'AND' versions then work.