Filter Macro

[thefonz37]

I'm building an employee scorecard, but I want to filter by only the relevant
results, so I want to place a filter on a range of data to eliminate the
values "0", "No data", and "No ranking".

When I tried to record the macro, I instead came up with the following code,
which didn't filter out the above 3 values, instead filtered FOR the
remaining values (which are subject to change, depending on a number of
things). Is there any way to correct this?

Sub macFilterScorecard()
'
' macFilterScorecard Macro
'

'
ActiveSheet.Range("$B$4:$C$172").AutoFilter Field:=2, Criteria1:=Array( _
"0.25", "0.65", "1", "104.00", "129", "137.38%", "5.29%", "5.50",
"7,778", "79.69", "98", _
"98.50"), Operator:=xlFilterValues
End Sub

 

[OssieMac]

If you want to exclude values "0", "No data", and "No ranking" then you need
to set the Custom filter to not equal these values.

In xl2007 click the filter dropdown, select Number filter -> Custom filter..

Earlier versions of xl click the dropdown arrow and select Custom

Then set the first parameter to Does not equal and place zero in the
adjacent field

Click And operator (probably default)

Set second parameter to Does not begin with and insert No in the second field.

Note that you can only have 2 so I have used No as the common value between
No data and No ranking. (I don't think you can have more than 2 parameters
but i stand correcting if someone knows how to add more.)

You can record the code. However, I suggest that you edit the range in the
code as below and that makes it generic for any number of rows in the
Autofilter range.

ActiveSheet.AutoFilter.Range.AutoFilter Field:=2, _
Criteria1:="<>0", Operator:=xlAnd, Criteria2:="<>No*"

 

[DND]

Sub macFilterScorecard()
‘Employee Score Card Filter Macro
‘Employee scorecard, Filter a range of data to eliminate the values "0", "No
data", and "No ranking"


Range("$B$4:$C$172.AutoFilter Field:=2, Criteria1:="<>0", Operator:=xlAnd,
Criteria2:="<>*N*"
End Sub

Of course this won't filter blank cells nor will it filter score card values
that display an error (#value, etc) as a result of a formmula.

For that you might try:

Range("B4:C172").AdvancedFilter Action:=xlFilterInPlace,
CriteriaRange:=Range("B1:C3"), Unique:=False

Where the values in B1 & C1 are the Column Headers that are in B4 & C4, and
C2 = 1, C2 = <1, C3 = >1