CODEWITHSUNDEEP
excelvbaadvanced-filter
tweaked9107
tweaked9107

Reputation: 11

Excel VBA Advanced Filter issue due to blank hyperlink criteria

I'm having a bit of a nightmare trying to set up a VBA code in Excel. I'm successfully able to filter out data based using 1 column on a criteria range that is a list of peoples names, provided I only select the criteria range to the bottom of the list of the names (for example $AM$2001:$AM$2100).

The issue is I want to go beyond that bottom of the list of names into the blank cells so that if I add any names in future it will automatically update accordingly. However, because the list is hyperlinked from a separate sheet, the criteria is picking up the 0's and showing all the blanks in the filter result.

How to adjust my code so that these "blank" cells or "0" cells don't show up? Will it just not work because of the hyperlink? My current code is as follows:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Range("$F$1:$F$2000").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range _ 
       ("$AM$2001:$AM$2200"), Unique:=False  
End Sub

As said, the issue seems to be because the list of names actually ends at AM2100, not AM2200. AM2101 to AM2200 are all filled with 0 from the hyperlinked sheet.

Upvotes: 1

Views: 248

Answers (1)

xn1
xn1

Reputation: 427

Try

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Range("$F$1:$F$2000").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range _ 
       ("$AM$2001:$AM$" & UsedRange.Rows.Count()), Unique:=False  
End Sub

if that still brings in too many cells (used range bigger than number of used rows in column AM try this

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Range("$F$1:$F$2000").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range _ 
       ("$AM$2001:$AM$" & Range("$AM$2001").End(xlDown).Row()), Unique:=False  
End Sub

Upvotes: 0

Related Questions