ExcelBanter - Toggle Autofilter

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Toggle Autofilter (https://www.excelbanter.com/excel-programming/371628-toggle-autofilter.html)

Dave

Toggle Autofilter

I'd like to display all of a list -or- only records with blank cells in the
8th column.
What code could I assign to a button that would toggle an autofilter?
Thank you.

Die_Another_Day

Toggle Autofilter

Try this:
Sub ToggleAutoFilter()
If Range("H1").Parent.AutoFilter.Filters(1).Criteria1 = "=" Then
Range("H1").AutoFilter Field:=8
Else
Range("H1").AutoFilter Field:=8, Criteria1:= "="
End If
End Sub

Place a button on your sheet from the Forms toolbar and choose the
macro "ToggleAutoFilter" to run when clicked.

Charles

Dave wrote:
I'd like to display all of a list -or- only records with blank cells in the
8th column.
What code could I assign to a button that would toggle an autofilter?
Thank you.

Die_Another_Day

Toggle Autofilter

I had to modify it slightly to handle the "All" Case without generating
an error.
Sub ToggleAutoFilter()
If Not Range("H1").Parent.AutoFilter.Filters(1).On Then
Range("H1").AutoFilter Field:=8, Criteria1:="="
Else
Range("H1").AutoFilter Field:=8
End If
End Sub

Charles

Die_Another_Day wrote:
Try this:
Sub ToggleAutoFilter()
If Range("H1").Parent.AutoFilter.Filters(1).Criteria1 = "=" Then
Range("H1").AutoFilter Field:=8
Else
Range("H1").AutoFilter Field:=8, Criteria1:= "="
End If
End Sub

Place a button on your sheet from the Forms toolbar and choose the
macro "ToggleAutoFilter" to run when clicked.

Charles

Dave wrote:
I'd like to display all of a list -or- only records with blank cells in the
8th column.
What code could I assign to a button that would toggle an autofilter?
Thank you.

Gord Dibben

Toggle Autofilter

Dave

ToolsMacroRecord New Macro.

Record yourself while doing the above.

Gord Dibben MS Excel MVP

On Mon, 28 Aug 2006 13:12:01 -0700, Dave wrote:

I'd like to display all of a list -or- only records with blank cells in the
8th column.
What code could I assign to a button that would toggle an autofilter?
Thank you.

Dave

Toggle Autofilter

Perhaps I've omited some critical info.
I'm running Excel 2000 on WinXP.
My header row is B4:I4.
Autofilters are on. (Visible)
I've copied the code below changing "H1" to "I4"
When run, it errors on the 1st line 'If Range...'
Displays "Application-Defined or Object-Defined error"
Any suggestions?
Thanks.

"Die_Another_Day" wrote:

Try this:
Sub ToggleAutoFilter()
If Range("H1").Parent.AutoFilter.Filters(1).Criteria1 = "=" Then
Range("H1").AutoFilter Field:=8
Else
Range("H1").AutoFilter Field:=8, Criteria1:= "="
End If
End Sub

Place a button on your sheet from the Forms toolbar and choose the
macro "ToggleAutoFilter" to run when clicked.

Charles

Dave wrote:
I'd like to display all of a list -or- only records with blank cells in the
8th column.
What code could I assign to a button that would toggle an autofilter?
Thank you.

Die_Another_Day

Toggle Autofilter

It looks like you used the first code I posted, I caught my mistake and
reposted. Here it is again:
Sub ToggleAutoFilter()
If Not Range("H1").Parent.AutoFilter.Filters(1).On Then
Range("H1").AutoFilter Field:=8, Criteria1:="="
Else
Range("H1").AutoFilter Field:=8
End If
End Sub

Try that version and let me know if you still have issues.

Charles

Dave wrote:
Perhaps I've omited some critical info.
I'm running Excel 2000 on WinXP.
My header row is B4:I4.
Autofilters are on. (Visible)
I've copied the code below changing "H1" to "I4"
When run, it errors on the 1st line 'If Range...'
Displays "Application-Defined or Object-Defined error"
Any suggestions?
Thanks.

"Die_Another_Day" wrote:

Try this:
Sub ToggleAutoFilter()
If Range("H1").Parent.AutoFilter.Filters(1).Criteria1 = "=" Then
Range("H1").AutoFilter Field:=8
Else
Range("H1").AutoFilter Field:=8, Criteria1:= "="
End If
End Sub

Place a button on your sheet from the Forms toolbar and choose the
macro "ToggleAutoFilter" to run when clicked.

Charles

Dave wrote:
I'd like to display all of a list -or- only records with blank cells in the
8th column.
What code could I assign to a button that would toggle an autofilter?
Thank you.

Mike Fogleman

Toggle Autofilter

It needs to be Filters(8).On.
Try this:

Sub ToggleAutoFilter()
If Not Sheet1.AutoFilterMode Then
Sheet1.Range("H1").AutoFilter
End If
If Not Range("H1").Parent.AutoFilter.Filters(8).On Then
Range("H1").AutoFilter Field:=8, Criteria1:="="
Else
Range("H1").AutoFilter Field:=8
End If
End Sub

Mike F
"Die_Another_Day" wrote in message
ups.com...
It looks like you used the first code I posted, I caught my mistake and
reposted. Here it is again:
Sub ToggleAutoFilter()
If Not Range("H1").Parent.AutoFilter.Filters(1).On Then
Range("H1").AutoFilter Field:=8, Criteria1:="="
Else
Range("H1").AutoFilter Field:=8
End If
End Sub

Try that version and let me know if you still have issues.

Charles

Dave wrote:
Perhaps I've omited some critical info.
I'm running Excel 2000 on WinXP.
My header row is B4:I4.
Autofilters are on. (Visible)
I've copied the code below changing "H1" to "I4"
When run, it errors on the 1st line 'If Range...'
Displays "Application-Defined or Object-Defined error"
Any suggestions?
Thanks.

"Die_Another_Day" wrote:

Try this:
Sub ToggleAutoFilter()
If Range("H1").Parent.AutoFilter.Filters(1).Criteria1 = "=" Then
Range("H1").AutoFilter Field:=8
Else
Range("H1").AutoFilter Field:=8, Criteria1:= "="
End If
End Sub

Place a button on your sheet from the Forms toolbar and choose the
macro "ToggleAutoFilter" to run when clicked.

Charles

Dave wrote:
I'd like to display all of a list -or- only records with blank cells
in the
8th column.
What code could I assign to a button that would toggle an autofilter?
Thank you.

Dave

Toggle Autofilter

This version doesn't error but the If statement continues to return True
so that it never reaches the Else clause.

"Die_Another_Day" wrote:

It looks like you used the first code I posted, I caught my mistake and
reposted. Here it is again:
Sub ToggleAutoFilter()
If Not Range("H1").Parent.AutoFilter.Filters(1).On Then
Range("H1").AutoFilter Field:=8, Criteria1:="="
Else
Range("H1").AutoFilter Field:=8
End If
End Sub

Try that version and let me know if you still have issues.

Charles

Dave wrote:
Perhaps I've omited some critical info.
I'm running Excel 2000 on WinXP.
My header row is B4:I4.
Autofilters are on. (Visible)
I've copied the code below changing "H1" to "I4"
When run, it errors on the 1st line 'If Range...'
Displays "Application-Defined or Object-Defined error"
Any suggestions?
Thanks.

"Die_Another_Day" wrote:

Try this:
Sub ToggleAutoFilter()
If Range("H1").Parent.AutoFilter.Filters(1).Criteria1 = "=" Then
Range("H1").AutoFilter Field:=8
Else
Range("H1").AutoFilter Field:=8, Criteria1:= "="
End If
End Sub

Place a button on your sheet from the Forms toolbar and choose the
macro "ToggleAutoFilter" to run when clicked.

Charles

Dave wrote:
I'd like to display all of a list -or- only records with blank cells in the
8th column.
What code could I assign to a button that would toggle an autofilter?
Thank you.

Dave

Toggle Autofilter

Thanks, but I'm looking for an If Statement that would allow a button to
toggle the filter on/off.

"Gord Dibben" wrote:

Dave

ToolsMacroRecord New Macro.

Record yourself while doing the above.

Gord Dibben MS Excel MVP

On Mon, 28 Aug 2006 13:12:01 -0700, Dave wrote:

I'd like to display all of a list -or- only records with blank cells in the
8th column.
What code could I assign to a button that would toggle an autofilter?
Thank you.

Mike Fogleman

Toggle Autofilter

Dave

Toggle Autofilter

Thanks very much to all for the help!
It's working well.

"Mike Fogleman" wrote:

It needs to be Filters(8).On.
Try this:

Sub ToggleAutoFilter()
If Not Sheet1.AutoFilterMode Then
Sheet1.Range("H1").AutoFilter
End If
If Not Range("H1").Parent.AutoFilter.Filters(8).On Then
Range("H1").AutoFilter Field:=8, Criteria1:="="
Else
Range("H1").AutoFilter Field:=8
End If
End Sub

Mike F

"Dave" wrote in message
...
I'd like to display all of a list -or- only records with blank cells in
the
8th column.
What code could I assign to a button that would toggle an autofilter?
Thank you.

All times are GMT +1. The time now is 06:39 AM.