Sunday, July 20, 2008

Check for existence of Filter using Excel VBA

Check if Range is Filtered / Check if Sheet has AutoFilter using Excel VBA

Checks for filter can be done at two levels

1. If Range/Sheet has AutoFilder

2. If Filter has been applied on any column

Sub Check_AutoFilter_IsPresent()

Dim oWS As Worksheet ' Worksheet Object

On Error GoTo Disp_Error

' ---------------------------------------------

' Coded by Shasur for www.vbadud.blogspot.com

' ---------------------------------------------

oWS = ActiveSheet

If Not oWS.AutoFilter Is Nothing Then

If oWS.FilterMode = True Then

MsgBox("Auto Filter On: Filter Mode On")

Else

MsgBox("Auto Filter On: Filter Mode Off")

End If

Else

MsgBox("Auto Filter Off")

End If

If Not oWS Is Nothing Then oWS = Nothing

' --------------------

' Error Handling

' --------------------

Disp_Error:

If Err <> 0 Then

MsgBox(Err.Number & " - " & Err.Description, vbExclamation, "VBA Tips & Tricks Examples")

Resume Next

End If

End Sub

See also:

Create AutoFilter with Multiple Criteria using Excel VBA

AutoFilter using Excel VBA

Check for existence of Filter using Excel VBA

Excel Filter Show All using VBA

Retrieve / Get First Row of Excel AutoFilter using VBA

3 comments:

  1. Anonymous8:44 AM

    SET ows=activesheet

    ReplyDelete
  2. Anonymous7:50 AM

    vVry nice, I was looking exactly for tihs code. In particular the part...
    ----
    If Not oWS.AutoFilter Is Nothing Then
    If oWS.FilterMode = True Then
    ----
    Thank you so much!!

    ReplyDelete
  3. Anonymous11:53 AM

    Worked for me except for adding "SET" for oWS. Thanks for the tip - Tim

    ReplyDelete

StumbleUpon
Share on Facebook
Related Posts Plugin for WordPress, Blogger...
Download Windows Live Toolbar and personalize your Web experience! Add custom buttons to get the information you care about most.