AutoFilter Macro to hide 0 and blank values

Question

I am trying to hide rows in a particular column with zero values and blank values after clicking a button. I have coded the following macro:

ActiveSheet.Range("$A$16:$C$2106").AutoFilter Field:=3, Criteria1:="<>0", Operator:=xlFilterValues

It correctly filters 0 values. However, it is not filtering about blank cells. Is this the correct code?

score 2 · Answer 1 · answered Oct 08 '19 at 15:50

2

You could try two criteria:

ActiveSheet.Range("$A$16:$C$2106").AutoFilter Field:=3, Criteria1:="<>0", Operator:=xlAnd, Criteria2:="*"

Noteworthy: It's recommended to try and stay away from using ActiveSheet and the likes.

answered Oct 08 '19 at 15:50

JvdV

I attempted this and it did not work as intended. It ended up hiding rows that had values – 90'sProSkater Oct 09 '19 at 17:07
@90'sProSkater, that's strange. Works as intended on my end. Maybe update your question with some of your sample data/screenshot etc. – JvdV Oct 09 '19 at 17:22
1

Criteria2 should be set to "<>" to filter out blanks as per @Jan's example below. As written, Criteria2:="*" filters out everything and you're left with nothing. – Neman Jun 27 '23 at 15:25

score 1 · Answer 2 · edited Jun 30 '23 at 00:55

1

I totally agree with JvdV, two operators is the way to go. For filtering out zero and blank (empty) you may want:

ActiveSheet.Range("$A$16:$C$2106").AutoFilter Field:=3, Criteria1:="<>0", Operator:=xlAnd, Criteria2:="<>"

Since edits must be at least 6 characters, edited Criteria2 to use named parameter syntax. Changed Criteria2="<>" to Criteria2:="<>"

edited Jun 30 '23 at 00:55

Neman

answered Oct 08 '19 at 15:58

Jan

Post-edit comment: @Jan's code worked. My mistake was not fully clearing the existing AutoFilter. ` With ActiveSheet.ListObjects("QuizResults") If Not .AutoFilter Is Nothing Then .AutoFilter.ShowAllData End With ` – Neman Jun 27 '23 at 15:22

2 Answers2