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!

AutoFiltering for cells that "Contain" certain text is restricted to 2 such "contains" values. For what you are trying to do, as I understand it, could possibly be done with AutoFilter via a work-around, but more directly with Advanced Filter instead.
Try this with a copy of your workbook.

In 'KEEP-unique' column J (or choose another available column) leave row 1 blank and in row 2 (ie in J2) put the formula
=LOOKUP(9.99E+307,SEARCH(FilterCriteria,Data!H5))

Now try this code (edit code if you didn't use column J above)

Sub FilterManyContains()
  Sheets("Data").Range("A4").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Sheets("KEEP-unique").Range("J1:J2"), Unique:=False
End Sub

To unfilter, use 'Clear' in the 'Sort & Filter' group on the 'Data' ribbon tab or we could have another code to do that.

If it is a 'must' to use Autofilter, post back.

 

[shella]

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

Peter,

Thanks for your suggestions but I couldn't get it to filter appropriately. I'm wondering if I strip back my request to a basic description, if it will widen the opportunity for additional vba possibilities...?

I'm trying to see if ANY words from column "X" (ex. "apple" or "pear" or "orange" etc.) appear in a range on another worksheet (ex. "A4:J100") then autofilter to show only those rows...

Is what I'm asking within the realm of possible? Or do I need to consider a different approach?

Thank you so much for your time and expertise!

 

[Peter_SSs]

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

In the other worksheet, can apple/pear/orange appear in any of the 10 column A:J? The code you posted above, was only looking in column H.

What about we just work with a single sheet to start with and you make a small set of sample dummy data with those three words in column X and 6 or 8 rows of data in columns A:J.
Post that sample data (my signature block below has help with that if needed) & explain which rows should be visible after the filter is applied.

One further question: If "apple" is one of the words in column X and "pineapple" is one of the words in a column you are trying to filter, should the pineapple row be visible (as it would normally be if you used *apple* in AutoFilter like in the code you posted) or hidden (since it doesn't contain the exact word apple)?

 

[shella]

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

Peter,

Thank you for your questions - particularly regarding Column H, as I should've explained myself in the initial post. Attached is the single sheet file, as suggested, with data in Columns A:J (partially populated) and Filter Criteria in Column "X". Answers to your questions are below:

1. The filter criteria can appear in any of the following columns (as of current build): A, E, F, or G. I was using H as a helper column because I believed (erroneously, I hope) that all possible filters had to reside in a single column for autofilter to work. As such, we can disregard Column H.

2. As of right now, the goal would be an exact match (apple = apple, apple <>= pineapple - hiding pineapple results). That said, I'm wondering if you would be so generous as to share with me both the exact match and the wildcard option, as I envision future iterations may want to use that capability and I'd like to learn how to code that!


Excel 2016 (Windows) 32 bit

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
DATA
Customer Engagement	Risk/Concern	Customer Engagement Risk/Concern
Competitive Intel	Metric	Competitive Intel Metric
Black Hat	Metric	Metric	Black Hat Metric Metric
Industry Day	Hot Button - Hope	Industry Day Hot Button - Hope
SME Interview	Hot Button - Fear	SME Interview Hot Button - Fear
Teammate Data Call	Ghost	Hot Button - Hope	Teammate Data Call Ghost Hot Button - Hope
Solutioning	Defend	Solutioning Defend
Other	Kudo	Other Kudo
Customer Engagement	Metric	Customer Engagement Metric
Competitive Intel	Proof	Competitive Intel Proof
Black Hat	Win Theme	Ghost	Black Hat Win Theme Ghost
Industry Day	Past Performance	Defend	Industry Day Past Performance Defend
SME Interview	Discriminator	Ghost	Kudo	SME Interview Discriminator Ghost Kudo
Teammate Data Call	Graphic	Defend	Metric	Teammate Data Call Graphic Defend Metric
Solutioning	Solution Element	Kudo	Proof	Solutioning Solution Element Kudo Proof
Other	Kudo	Metric	Win Theme	Other Kudo Metric Win Theme
Industry Day	Metric	Industry Day Metric
SME Interview	Proof	SME Interview Proof
Teammate Data Call	Solution Element	Teammate Data Call Solution Element
Solutioning	Metric	Solutioning Metric

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] "]Filter Criteria[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] "]Risk/Concern[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #003399"]Source[/TD]
[TD="bgcolor: #003399"]Name[/TD]
[TD="bgcolor: #003399"]Intel Description (or Question)[/TD]
[TD="bgcolor: #003399"]Intel (or Answer)[/TD]
[TD="bgcolor: #003399"]Tag[/TD]
[TD="bgcolor: #003399"]Tag 2[/TD]
[TD="bgcolor: #003399"]Tag 3[/TD]
[TD="bgcolor: #003399"]Helper Column[/TD]
[TD="bgcolor: #003399"]SOW/PPW Section[/TD]
[TD="bgcolor: #003399"]Prop Section[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] "]Metric[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] "]Hot Button - Hope[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] "]Hot Button - Fear[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] "]Ghost[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] "]Defend[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] "]Kudo[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] "]Proof[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] "]Win Theme[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] "]Past Performance[/TD]

[TD="align: center"]12[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] "]Discriminator[/TD]

[TD="align: center"]13[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] "]Graphic[/TD]

[TD="align: center"]14[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] "]Solution Element[/TD]

[TD="align: center"]15[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] "]Customer Engagement[/TD]

[TD="align: center"]16[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] "]Competitive Intel[/TD]

[TD="align: center"]17[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] "]Black Hat[/TD]

[TD="align: center"]18[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] "]Industry Day[/TD]

[TD="align: center"]19[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] "]SME Interview[/TD]

[TD="align: center"]20[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] "]Teammate Data Call[/TD]

[TD="align: center"]21[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] "]Solutioning[/TD]

[TD="align: center"]22[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] "]Other[/TD]

[TD="align: center"]23[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"][/TD]

[TD="align: center"]24[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"][/TD]

[TD="align: center"]25[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"][/TD]

</tbody>

Data​

Thank you so very much for your time and brainpower!

Best,
Shella

 

[Peter_SSs]

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

I'm not sure how good/representative that sample is? By my reckoning every row of the data contains one of the filter criteria so filtering would not hide any of the rows. Is that correct?

 

[shella]

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

Peter,

It actually is intended. I'm using just a skeleton dataset to set up the table with macros to perform how I want it to perform before building out. It will have a button for each filter criteria that will show those rows associated with that criteria (ex. "Apple" shows apple, and if both "Apple" and "Orange" are selected both of those rows will show). Does that make sense?

Thanks!

Shella

 

[Peter_SSs]

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

Does that make sense?

I understand what you are saying (I think) but when testing a filtering routine, it makes sense to me that some rows should end up hidden and some visible. If they all remain visible, you don't actually know if your code has done anything.

Therefore I have altered your sample considerably for testing and posting back here.
- I have removed the helper column that you didn't really want anyway. However I inserted a new column to keep the FilterCriteria in column X.
- I have re-defined FilterCriteria to refer to X2:X6 (The FilterCriteria range must have no blank cells in it)
- I have included the apple/pineapple example to demonstrate clearly (I hope) the two versions of the code you requested.
- Note that I have hidden many of the columns to keep my screen shot smaller.
- Range Y1:Y2 is also used (briefly) as a helper in my code.

A) To filter as you were filtering with that helper column (where pineapple would show up even if the filter criteria included only apple), use the code as below. I have marked yellow in column R the rows that remained visible after that filter.

B) To filter for rows that contain cells exactly as per FilterCriteria (pineapple would be hidden), swap to the alternate line in the code that is currently commented out. I have marked blue in column S the rows that remained visible after that filter.

Sub Adv_Filter()
  Dim rCrit As Range
  
  Set rCrit = Range("Y1:Y2")
  
  rCrit.Cells(2).Formula = "=LOOKUP(9.99E+307,SEARCH(FilterCriteria,TEXTJOIN("" "",TRUE,A4,E4:G4)))"
'  rCrit.Cells(2).Formula = "=LOOKUP(9.99E+307,SEARCH(""|""&FilterCriteria&""|"",""|""&TEXTJOIN(""|"",TRUE,A4,E4:G4)&""|""))"

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

To re-display all the rows, click 'Clear' in the 'Sort & Filter' group on the 'Data' ribbon tab or run this code:

Sub Clear_Adv_Filter()
  If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData
End Sub

Excel Workbook
	A	E	F	G	H	I	J	R	S	T	U	X	Y
1	DATA											Filter Criteria
2												Risk/Concern
3	Source	Tag	Tag 2	Tag 3	SOW/PPW Section	Prop Section						Metric
4	Customer Engagement	Risk/Concerns										Hot Button - Hope
5	Competitive Intel	Metric										apple
6	apple	xxx										Solution
7	pineapple	xxx
8	SME Interview	Hot Button - Fear
9	Teammate Data Call	Ghost	Hot Button - Hope
10	Solutioning	Defend
11	Other	Kudo
12	Customer Engagement	Metrical
13	Competitive Intel	Proof
14	Black Hat	Win Theme		Ghost
15	Industry Day	Past Performance		Defend
16	SME Interview	Discriminator	Ghost	Kudo
17	Teammate Data Call	Graphic	Defend	Metric
18	Solutioning	Solution Element	Kudo	Proof
19	Other	Kudo	Metric	Win Theme
20	Industry Day	Metric
21	SME Interview	Proof
22	Teammate Data Call	Solution Element
23	Solutioning	Metric
24
Data

 

[shella]

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 for seeing past my inept request (dataset with filter criteria in each row) and providing both versions of code that work brilliantly!! This has been an intractable problem for me, and I'm so grateful for your help!

Because I like to learn about the actual code to avoid having to ask for help in the same area, could you perhaps share with me why that autofilter line works or point me in the right direction for a google search that would allow me to read up on it/learn more about it?

Thank you again SO very much!!

 

[shella]

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

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