Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Apply more than one filter in macro/vb code
**Re-Posted**
I need code that enables a user to 'turn on' one or more filters in a spreadsheet. Currently have a btn that has statement if ActiveSheet.FilterMode Then Selection.AutoFilter Field:=27 else Selection.AutoFilter Field:=27, Criteria1:="=" (thus btn turns filter on and off) Also have simular statement in other btns, but need more that one btn to be able to work at same time. Many thanks for everyones help in this. **Re-Posted** Yes. If I manually apply the change it works... After the first filter is applied, there is still records (rows) that could be filtered out by the second.. but it does not work. Also whwn a filter is aplied the drop down filter goes blue, but of cause mine does not work.. please help. -----Original Message----- Can you manually apply the two filters to your data and get a change. It sounds to me that the first filter applied has eliminated any records that the second filter would affect. In otherwords, there are no records that have a blank in column 27 and a zero in column 26. -- Regards, Tom Ogilvy "Stepnen" wrote in message ... Hi Cecil, thanks for your help, but I can't seem to get this to compile.. My range is named as ReportData The _ does not compile ...so Ive deleted it, then it provides the error that AutoFilter is an "invalid use of property". -----Original Message----- Stepnen, Try this Sub FilterTest() Range("TestRange").SpecialCells(xlVisible) _ ..AutoFilter Field:=Range("I1").Value, _ Criteria1:=Range("K1").Value End Sub "TestRange" is the name given to the Range HTH Cecil "Stepnen" wrote in message ... Hi all. I have a spreadsheet whith approx 30 colums, all theses have an autofilter applied. I also have buttons that users can click on to apply a filter. When user clicks on btn filter; Selection.AutoFilter Field:=27, Criteria1:="=" Then when user applies a second filter (code as below), nothing happens. Selection.AutoFilter Field:=26, Criteria1:="0" Each filter works fine individually, but not together. Does anyone know why? and what solutions there might be? Thanks in advance. Steve. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Apply more than one filter in macro/vb code
So do you want something like a toggle?
Button1: If Worksheets("Sheet1").AutoFilter.Filters(27).On Then Selection.AutoFilter Field:=27 Else Selection.AutoFilter Field:=27, Criteria1:="=" End If Button2: If Worksheets("Sheet1").AutoFilter.Filters(26).On Then Selection.AutoFilter Field:=26 Else Selection.AutoFilter Field:=26, Criteria1:="0" End If "Stepnen" wrote in message ... **Re-Posted** I need code that enables a user to 'turn on' one or more filters in a spreadsheet. Currently have a btn that has statement if ActiveSheet.FilterMode Then Selection.AutoFilter Field:=27 else Selection.AutoFilter Field:=27, Criteria1:="=" (thus btn turns filter on and off) Also have simular statement in other btns, but need more that one btn to be able to work at same time. Many thanks for everyones help in this. **Re-Posted** Yes. If I manually apply the change it works... After the first filter is applied, there is still records (rows) that could be filtered out by the second.. but it does not work. Also whwn a filter is aplied the drop down filter goes blue, but of cause mine does not work.. please help. -----Original Message----- Can you manually apply the two filters to your data and get a change. It sounds to me that the first filter applied has eliminated any records that the second filter would affect. In otherwords, there are no records that have a blank in column 27 and a zero in column 26. -- Regards, Tom Ogilvy "Stepnen" wrote in message ... Hi Cecil, thanks for your help, but I can't seem to get this to compile.. My range is named as ReportData The _ does not compile ...so Ive deleted it, then it provides the error that AutoFilter is an "invalid use of property". -----Original Message----- Stepnen, Try this Sub FilterTest() Range("TestRange").SpecialCells(xlVisible) _ ..AutoFilter Field:=Range("I1").Value, _ Criteria1:=Range("K1").Value End Sub "TestRange" is the name given to the Range HTH Cecil "Stepnen" wrote in message ... Hi all. I have a spreadsheet whith approx 30 colums, all theses have an autofilter applied. I also have buttons that users can click on to apply a filter. When user clicks on btn filter; Selection.AutoFilter Field:=27, Criteria1:="=" Then when user applies a second filter (code as below), nothing happens. Selection.AutoFilter Field:=26, Criteria1:="0" Each filter works fine individually, but not together. Does anyone know why? and what solutions there might be? Thanks in advance. Steve. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Any way to apply a filter automatically after changing a cell? | Excel Discussion (Misc queries) | |||
keep total formulas / rows when apply filter | Excel Discussion (Misc queries) | |||
Can you apply the auto filter to data that are filtered? | Excel Worksheet Functions | |||
Filter Date Code for macro | Excel Discussion (Misc queries) | |||
Macro or VB to apply filter in spreadsheet | Excel Programming |