I am trying to filter out all results on Column 5 except for one called "Pending". Often, there are no "Pending" variables on the report, so I should be left with only blanks and column headers. Every time I run the code though, it returns a single result showing a variable called "Inactive" which should be filtered out. All the other "Active" and "Inactive" results are filtered out, but it seems to think that SOMETHING has to display since it can’t all be blank.
I read that sometimes you can use "=" to represent blanks in the autofilter, but it doesn’t appear to work.
How do I tell VBA that I’m okay if the variable I am looking for doesn’t exist on this report and it’s okay to display blank results?
Current code:
Sub PepperH()
Worksheets("Sheet2").Select
Range("A9:L3000").AutoFilter Field:=5, Criteria1:="Pending", Operator:=xlOr, Criteria2:="="
'Should only show pending accounts- headers run A1-A8 so we need to start after that
End Sub
End result of that code is the report looks like this: vast majority filtered out, but still leaving one result in Column E that shouldn’t be there
>Solution :
Notice that row 9 is the visible row, and the filter dropdown buttons are on row 9.
Range("A9:L3000").AutoFilter means you are using row 9 as your header row.
Range("A8:L3000").AutoFilter is probably what you want.