Results 1 to 7 of 7

VBA Code to Force Excel Autofilter Off (EL-97on Win-95)

This is a discussion on VBA Code to Force Excel Autofilter Off (EL-97on Win-95) within the Excel Questions forums, part of the Question Forums category; I have used some VBA code to set the Autofilter on in Excel. The AutoFilter works fine the first time ...

  1. #1
    Board Regular milesUK's Avatar
    Join Date
    Jan 2003
    Location
    Cheshire, England on "..an utterly insignificant little blue-green planet".
    Posts
    388

    Default VBA Code to Force Excel Autofilter Off (EL-97on Win-95)

    I have used some VBA code to set the Autofilter on in Excel. The AutoFilter works fine the first time the code is run but if the code is run again even though the filter buttons appear they will not 'press' (and all the filter buttons show black down-arrows on grey backgrounds with no filtering actually applied) and the filter cannot be used. :x
    Data-->Filter-->AutoFilter turns it off OK but I can not get VBA to ensure the filter is off before applying it. please.
    Miles

  2. #2
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    69,371

    Default Re: VBA Code to Force Excel Autofilter Off (EL-97on Win-95)

    This:

    ActiveSheet.Cells.AutoFilter

    will turn it off if it's on.

  3. #3
    Board Regular milesUK's Avatar
    Join Date
    Jan 2003
    Location
    Cheshire, England on "..an utterly insignificant little blue-green planet".
    Posts
    388

    Default Re: VBA Code to Force Excel Autofilter Off (EL-97on Win-95)

    Andrew, That was fast thanks. I have tried the
    Code:
    ActiveSheet.Cells.AutoFilter
    but alas it toggles the state of the filter rather than forcing it off regardless of its on/off condition.
    Miles

  4. #4
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    69,371

    Default Re: VBA Code to Force Excel Autofilter Off (EL-97on Win-95)

    I thought you said it was on. Anyway:

    Code:
    If ActiveSheet.AutoFilterMode Then
        ActiveSheet.Cells.AutoFilter
    End If

  5. #5
    Board Regular milesUK's Avatar
    Join Date
    Jan 2003
    Location
    Cheshire, England on "..an utterly insignificant little blue-green planet".
    Posts
    388

    Default Re: VBA Code to Force Excel Autofilter Off (EL-97on Win-95)

    Andrew, Thanks once again. The AutoFilterMode was just what I needed but didn't spot it in the help index! All now works fine if I use
    Code:
    Selection.AutoFilter
    however the following code displays the down arrows but they are locked and unusable and the filtering is NOT applied.
    Code:
    Range("A1:L1").AutoFilter
    Perhaps Range("A1:L1") is not a valid range objetc in this case.
    Miles

  6. #6
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    69,371

    Default Re: VBA Code to Force Excel Autofilter Off (EL-97on Win-95)

    That's because you are only filtering one row. Try:

    Range("A1:L1").CurrentRegion.AutoFilter

  7. #7
    Board Regular milesUK's Avatar
    Join Date
    Jan 2003
    Location
    Cheshire, England on "..an utterly insignificant little blue-green planet".
    Posts
    388

    Default Re: VBA Code to Force Excel Autofilter Off (EL-97on Win-95)

    Doh! I had initially recorded this bit of code to get an idea of the syntax but did not notice that I had selected the cells A1:L1 only when I really intended to select the columns A:L. Thanks for spotting it.
    Miles

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