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.

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.