• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Turn Off Table Filters if they are on

glennpc

Member
I have a table, and as part of setting up a print job to print a report of that table in VBA, I want to turn off all filtering if its on, but do nothing if its already off. In either case, at the end of the sub, I want to deselect the table (by selecting range "A1"). The problem I'm having is my code just toggles the filtering-- if its on it turns it off, but if its off, it turns it on. Here's my code:


Sub TurnOffFilter()

'

' TurnOffFilter Macro

'

If ActiveSheet.FilterMode = True Then

Range("A1").Select

Else

Range("Table2").Select

Selection.AutoFilter


Range("A1").Select

End If

End Sub


Any ideas what I'm doing wrong here?
 
Sorry-- forgot the backtick characters. Here's the code:

[pre]
Code:
Sub TurnOffFilter()
'
' TurnOffFilter Macro
'
If ActiveSheet.FilterMode = True Then
Range("A1").Select
Else
Range("Table2").Select
Selection.AutoFilter

Range("A1").Select
End If
End Sub
[/pre]
 
Hi, glennpc!


Try this:

-----

[pre]
Code:
If ActiveSheet.AutoFilterMode Then
If ActiveSheet.FilterMode Then
ActiveSheet.ShowAllData
End If
ElseIf ActiveSheet.FilterMode Then
ActiveSheet.ShowAllData
End If
[/pre]
-----

from: http://www.ozgrid.com/forum/showthread.php?t=47795


Regards!


PS: next time you can edit your original post and replace the un-indented text by the aligned text with backticks too.
 
Thanks SirJB7. This seems to work, but it still leaves the filter arrows on my table's header row. How would I amend the code you gave me to eliminate those arrows as the last thing the macro does?
 
Hi, glennpc!

If you had read the posted link you would have found this, that maybe is your answer:

-----

Code:
If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False

-----

Regards!
 
Back
Top