Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filter Macro
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filter Macro
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*" -- Regards, OssieMac "thefonz37" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filter Macro
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 "thefonz37" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how can this filter be done in a macro? | Excel Discussion (Misc queries) | |||
Filter Macro | Excel Programming | |||
Need macro to filter, create tab on filter and copy/paste | Excel Programming | |||
Need macro to filter, create tab on filter and copy/paste | Excel Programming | |||
Need a filter macro | Excel Discussion (Misc queries) |