Filter out (hide) "0" value when other values are dynamic

[usermrex]

Hi,

Column A has values that are dynamic references that change based on other inputs. Some rows will often have no data, so the value for these in column A is "0".

I need a macro that will filter OUT all the rows with "0".

The problem is that when I record my macro, it actually filters IN the other values, which are specific to when the macro is first recorded. So when the values change, none are found because it is looking for the ones originally specified in the macro.

ActiveSheet.Range("$A$8:$AL$101").AutoFilter Field:=1, Criteria1:=Array( _
"Marker 001", "Marker 002", "Marker 003", "Marker 004", "Marker 005"), Operator:= _
xlFilterValues

The "Markers" will have different names as the spreadsheet is used.

Can this be fixed relatively easily? I know some advise against using filters altogether, but this is not an option for me...

Thanks very much,
Sara

 

[dermie_72]

Did you try adding "<>0" to the array?

 

[usermrex]

Thanks Dermie,

Unfortunately that didn't work. I tried adding "<>0" and again entering ONLY "<>0", but for both I got the message "run-time error '1004': AutoFilter method of Range class failed"

Sara

 

[dermie_72]

Hi Sara,

Try adding:

ActiveSheet.Range("$A$8:$AL$101").AutoFilter Field:=1, Criteria1:="<>0", _
Operator:=xlAnd

just below the one you have, so it picks it up as the and statement

 

[usermrex]

It worked! YAAAY!! Thank you SO SO much! I love this forum!