Advanced Filter

[Davidns]

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?

 

[Yongle]

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 ?

 

[Davidns]

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.

 

[Peter_SSs]

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.

Perhaps there is a better way to clear the advanced filter items?

Not that I am aware of.

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?

 

[Davidns]

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.

 

[Peter_SSs]

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.

 

[Yongle]

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)

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

 

[Davidns]

Thanks folks! Much appreciated!