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!

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.

You have MADE my WEEK!! Thank you so much for catching my error there - it works brilliantly now, as you knew it would! Thank you thank you thank you!!!!!!!!!!

Best,
Shella

 

[Peter_SSs]

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

You have MADE my WEEK!! Thank you so much for catching my error there - it works brilliantly now, as you knew it would! Thank you thank you thank you!!!!!!!!!!

Best,
Shella

You're welcome. Glad we got there in the end.

BTW, I don't think you need Sheet2.Activate in those codes. I'm assuming that you will be triggering the codes from those buttons on 'Data'. Since the buttons are on Sheet2 ('Data'), it must already be the ActiveSheet when the button is clicked.

 

[shella]

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

Peter,

Quick question (I hope!) - do you know where/how in our adv_filter code I could insert a MsgBox in the event that the filter hid all rows (aka no results)? I'm not quite sure how to attach an IF statement to the filter. Thanks!!

Shella

 

[Peter_SSs]

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

In that event, do you still want all the rows hidden as well as the msgbox or not hide anything & show msgbox?

 

[shella]

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

Peter,

I'd like to leave the rows hidden (execute the adv_filter as currently written) and have a msgbox pop up informing user there are no results please. Thank you!!

 

[Peter_SSs]

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

Try

Sub Adv_Filter_v3()
  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
    rCrit.ClearContents
    If .Columns(1).SpecialCells(xlVisible).Count = 1 Then MsgBox "No results for current filter criteria"
  End With
End Sub

 

[shella]

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

Try

This worked brilliantly - thank you AGAIN so much!

 

[Peter_SSs]

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

This worked brilliantly - thank you AGAIN so much!