Dynamic Named Range as Array for AutoFilter not working

shella · Aug 18, 2018

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!

Code:

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 · Aug 18, 2018

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)

Code:

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 · Aug 19, 2018

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 · Aug 19, 2018

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 · Aug 20, 2018

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
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 · Aug 20, 2018

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 · Aug 21, 2018

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 · Aug 21, 2018

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

shella said:
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.

Rich (BB code):

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:

Rich (BB 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 · Aug 22, 2018

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 · Aug 22, 2018

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!!

Dynamic Named Range as Array for AutoFilter not working

shella

New Member

Excel Facts

Peter_SSs

MrExcel MVP, Moderator

shella

New Member

Peter_SSs

MrExcel MVP, Moderator

shella

New Member

Peter_SSs

MrExcel MVP, Moderator

shella

New Member

Peter_SSs

MrExcel MVP, Moderator

shella

New Member

shella

New Member

Similar threads

Forum statistics

Share this page

Dynamic Named Range as Array for AutoFilter not working

New Member

Excel Facts

MrExcel MVP, Moderator

New Member

MrExcel MVP, Moderator

New Member

MrExcel MVP, Moderator

New Member

MrExcel MVP, Moderator

New Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock