Results 1 to 8 of 8

Thread: Advanced Filter
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    May 2011
    Posts
    119
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Advanced Filter

    I am starting to use Advance Filtering, and notice that when I try to clear the filter, the table formatting is cleared as well. How can I just remove the Advance Filter settings without disturbing anything else on the table?

  2. #2
    Board Regular
    Join Date
    Mar 2015
    Posts
    4,059
    Post Thanks / Like
    Mentioned
    73 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Advanced Filter

    I am unable to recreate your problem - possibly because I am doing something different


    1 Which version of Excel are you using?

    2 Are you applying advanced filter against a standard range or a structured table ?

    3 Is the data being "filtered in place" or copied to another sheet ?

    4 How are you clearing the filter ?

    5 Which type of formatting is being cleared ?
    - all formatting
    - conditional formatting ?
    - formatting in specific columns ?

  3. #3
    Board Regular
    Join Date
    May 2011
    Posts
    119
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Advanced Filter

    Hi, thanks for your response:

    1) Office 365
    2) Structured Table
    3) Filtered in place
    4) by pressing the "clear" choice above the advanced filter menu item
    5) Seems to be just the formatting (alternating colors). However, now I see that if I make a change in the table, the colors reappear. Quite odd.

    Perhaps there is a better way to clear the advanced filter items? Seems strange that the menu for those choices doesn't include cancelling out the filter.

  4. #4
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    41,425
    Post Thanks / Like
    Mentioned
    100 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Advanced Filter

    Quote Originally Posted by Davidns View Post
    5) Seems to be just the formatting (alternating colors). However, now I see that if I make a change in the table, the colors reappear. Quite odd.
    I found the same thing. Note though that the AutoFilter arrows & capability that normally comes with the structured table do not reappear - at least they didn't for me.


    Quote Originally Posted by Davidns View Post
    Perhaps there is a better way to clear the advanced filter items?
    Not that I am aware of.


    Quote Originally Posted by Davidns View Post
    Seems strange that the menu for those choices doesn't include cancelling out the filter.
    Not sure what you mean here as that is exactly what the 'Clear' is isn't it?
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  5. #5
    Board Regular
    Join Date
    May 2011
    Posts
    119
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Advanced Filter

    What I mean is that apparently Clear does more than just cancel out the Advanced Filter, given the other things it tends to clear, as we both experienced. So, it seems to me the Advance Filter should be able to be cleared directly, without affecting anything else.

  6. #6
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    41,425
    Post Thanks / Like
    Mentioned
    100 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Advanced Filter

    Quote Originally Posted by Davidns View Post
    So, it seems to me the Advance Filter should be able to be cleared directly, without affecting anything else.
    In relation to the formatting I definitely agree.

    In relation to the loss of AutoFilter, note that it is the application of Advanced filter that removes the AutoFilter, not the clearing of Adv Fltr.
    I think the logic is that if you had both types of filter going there could be awful confusion about what to show and what to hide if the two types of filters were trying to show different data.

    Note also that it is not only Adv Fltr that has this sort of effect, it also works the other way around. If you have Adv Fltr going on a structured table and you manually re-apply the AutoFilter, that immediately removes the Adv Fltr.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  7. #7
    Board Regular
    Join Date
    Mar 2015
    Posts
    4,059
    Post Thanks / Like
    Mentioned
    73 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Advanced Filter

    Here is a simple VBA workaround to refresh the table style when clear button is pressed

    Unfortunately filtering does not trigger an event
    - but we can create one by placing SUBTOTAL formula in a cell
    - a recalculation of SUBTOTAL formula triggers event Worksheet_Calculate


    Place code in sheet module (will not work anywhere else)
    Code:
    Private Sub Worksheet_Calculate()
        With Me.ListObjects(1)
            If .Range.SpecialCells(xlCellTypeVisible).CountLarge = .Range.CountLarge Then
                .TableStyle = .TableStyle
            End If
        End With
    End Sub
    Below is a simple example which works for me - adapt to match your needs
    - Table with 2 columns in A5:B10
    - Criteria Range is A1:B2
    - formula in to trigger an event is in D1
    =SUBTOTAL(3,Table1[Player Names])

    Excel 2016 (Windows) 32 bit
    A
    B
    C
    D
    E
    1
    Player Names Goals
    5
    =SUBTOTAL(3,Table1[Player Names])
    2
    15
    3
    4
    5
    Player Names Goals
    6
    Name01
    10
    7
    Name02
    15
    8
    Name03
    10
    9
    Name04
    15
    10
    name05
    2
    Sheet: Sheet1

  8. #8
    Board Regular
    Join Date
    May 2011
    Posts
    119
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Advanced Filter

    Thanks folks! Much appreciated!

Some videos you may like

User Tag List

Tags for this Thread

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
  •