Results 1 to 6 of 6

VBA - turn off Autofilter

This is a discussion on VBA - turn off Autofilter within the Excel Questions forums, part of the Question Forums category; Hello All, I need help with some VBA . I have written a macro which utilises Autofilters, but it seems ...

  1. #1
    Board Regular
    Join Date
    Apr 2005
    Location
    Sydney
    Posts
    135

    Default VBA - turn off Autofilter

    Hello All,

    I need help with some VBA.

    I have written a macro which utilises Autofilters, but it seems to fail when autofilter is already turned on (even without criteria) before the macro is run.

    Is there vba code to turn of any autofilers (if any exists)?
    I'd like to stick this at the front of my macro to prevent this error from happening...

    Thanks in advanced!

  2. #2
    Board Regular kieran's Avatar
    Join Date
    Oct 2002
    Location
    Perth, Western Australia
    Posts
    396

    Default

    The simple way is to use range.autofilter.

    This toggles the autofilter on/off.

    The range object should be set to something like ActiveSheet.Autofilter.Range to turn off an existing filter.

    HIH
    Kieran

  3. #3
    Board Regular
    Join Date
    Apr 2005
    Location
    Sydney
    Posts
    135

    Default

    But the provlem with that code there is that, If filter is "off" to start it will then fail...

    I was hoping to find a "universal" way of turning the filter off (regardless of it starting as being on or off)...

  4. #4
    Board Regular kieran's Avatar
    Join Date
    Oct 2002
    Location
    Perth, Western Australia
    Posts
    396

    Default

    Here is a macro I use to add/remov filters based on the current cell status.

    It will allow for filter on (turn filter off if the active cell is outside the fitler range, or filter by the current cell value if the active cell is insid ethe range) and , filter off - create a new filter for the current region or selection.

    Code:
    Sub AutofilterRange(Optional ByVal Scope As Range)
        '
        ' Autofilter Macro
        '
        Dim CurrCell  As Range
        Dim CurrCell1 As Range
        Dim CurrRegion As Range
        Dim CurrCol   As Integer
        Dim TwoCriteria As Boolean
    
        If Not WorkbookActive Then
            MsgBox "There are no suitable windows active to work with", vbInformation, "I can't work with this!"
            Exit Sub
        End If
    
        Set CurrCell = Selection.Cells(1, 1)
        Set CurrCell1 = Selection.Cells(2, 1)
        If Selection.Rows.Count > 1 Then
            TwoCriteria = True
        Else
            TwoCriteria = False
        End If
    
        If Scope Is Nothing Then
            If ActiveSheet.AutoFilterMode Then       ' there is an autofilter applied
                Set CurrRegion = ActiveSheet.Autofilter.Range
            Else                                     ' no filter applied - need to identify region to filter
                If Selection.Columns.Count * Selection.Rows.Count = 1 Then
                    Set CurrRegion = Selection.CurrentRegion
                Else
                    Set CurrRegion = Selection
                End If
            End If
        Else
            Set CurrRegion = Scope
        End If
    
    
        CurrCol = CurrCell.Column - CurrRegion.Column + 1
    
        If ActiveSheet.AutoFilterMode = True Then    ' there is a filter in place
            If Application.Intersect(CurrCell, ActiveSheet.Autofilter.Range) Is Nothing Then
                ' Active selection NOT in filtered list"
                CurrRegion.Autofilter                ' turn off autofilter
                Call Message("Autofilter has been turned OFF", iClearMessage:=10)
            Else
                'Active selection in filtered list - add or remove criteria
                If ActiveSheet.Autofilter.Filters(CurrCol).On Then    'filter already in place for the column
                    CurrRegion.Autofilter field:=CurrCol    ' remove filter from column
                    Message ("Autofilter has been removed from the current column.")
                Else                                 ' not currenty a filtered colum so add a filter
                    If TwoCriteria Then
                        CurrRegion.Autofilter field:=CurrCol, Criteria1:=CurrCell.Text, _
                                              Operator:=xlOr, Criteria2:=CurrCell1.Text    ' add filters to column
                        Call Message("Autofilter has been added to the current column." & _
                                     "Filter criteria is : =" & _
                                     CurrCell.Text & " OR " & CurrCell1.Text, 10)
                    Else
                        CurrRegion.Autofilter field:=CurrCol, Criteria1:=CurrCell.Text    ' add filter to column
                        Call Message("Autofilter has been added to the current column." & _
                                     "Filter criteria is : =" & _
                                     CurrCell.Text, 10)
                    End If
                End If
            End If                                   ' processing where an autofilter exists
        Else                                         ' create filter for the list
            If (CurrRegion.Columns.Count * CurrRegion.Rows.Count) > 1 Then
                CurrRegion.Autofilter
                Call Message("Autofilter has been turned ON for the current region", 10)
            Else
                MsgBox "You must select an area with data to autofilter." & vbCrLf & _
                       "Please select an area with data and retry.", _
                       vbOKOnly, "Cannot apply AutoFilter"
            End If                                   ' turn on autofilter
        End If                                       ' autofilter edit or create
    
        Set CurrCell = Nothing
        Set CurrCell1 = Nothing
        Set CurrRegion = Nothing
    End Sub
    Kieran

  5. #5
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,008

    Default

    I was hoping to find a "universal" way of turning the filter off (regardless of it starting as being on or off)...
    Simple and foolproof:

    Activesheet.AutoFilterMode = False

  6. #6
    Board Regular
    Join Date
    Apr 2005
    Location
    Sydney
    Posts
    135

    Default

    Simple and foolproof:

    Activesheet.AutoFilterMode = False
    Thank you! this is exactly what I was looking for...

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com