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
1	DATA																							Filter Criteria
2																								Risk/Concern
3	Source	Name	Intel Description (or Question)	Intel (or Answer)	Tag	Tag 2	Tag 3	Helper Column	SOW/PPW Section	Prop Section														Metric
4	Customer Engagement			1	Risk/Concern			Customer Engagement Risk/Concern																Hot Button - Hope
5	Competitive Intel			1	Metric			Competitive Intel Metric																Hot Button - Fear
6	Black Hat			1	Metric	Metric		Black Hat Metric Metric																Ghost
7	Industry Day			1	Hot Button - Hope			Industry Day Hot Button - Hope																Defend
8	SME Interview			1	Hot Button - Fear			SME Interview Hot Button - Fear																Kudo
9	Teammate Data Call			1	Ghost	Hot Button - Hope		Teammate Data Call Ghost Hot Button - Hope																Proof
10	Solutioning			1	Defend			Solutioning Defend																Win Theme
11	Other			1	Kudo			Other Kudo																Past Performance
12	Customer Engagement			1	Metric			Customer Engagement Metric																Discriminator
13	Competitive Intel			1	Proof			Competitive Intel Proof																Graphic
14	Black Hat			1	Win Theme		Ghost	Black Hat Win Theme Ghost																Solution Element
15	Industry Day			1	Past Performance		Defend	Industry Day Past Performance Defend																Customer Engagement
16	SME Interview			1	Discriminator	Ghost	Kudo	SME Interview Discriminator Ghost Kudo																Competitive Intel
17	Teammate Data Call			1	Graphic	Defend	Metric	Teammate Data Call Graphic Defend Metric																Black Hat
18	Solutioning			1	Solution Element	Kudo	Proof	Solutioning Solution Element Kudo Proof																Industry Day
19	Other			1	Kudo	Metric	Win Theme	Other Kudo Metric Win Theme																SME Interview
20	Industry Day			1	Metric			Industry Day Metric																Teammate Data Call
21	SME Interview			1	Proof			SME Interview Proof																Solutioning
22	Teammate Data Call			1	Solution Element			Teammate Data Call Solution Element																Other
23	Solutioning			1	Metric			Solutioning Metric
24
25

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