How to clear filters in Sheet (analysis for office 2.2)

[Former Member]

HI Experts,

Searched blogs before posting

https://archive.sap.com/discussions/thread/3561684

I have a requirement to clear all filters which was select by user and give him freedom to select another selection

i tried for options but i dont see any option so i tried using macro but it was not working could anyone help me with these

<code>Sub Macro1()
    Cells.AutoFilter
EndSub

[reganmacdonald]

Your code does not look correct when it comes to the syntax.

DS2 should be DS_2 (as all the standard datasources are DS_1, DS_2, DS_3 etc). If you have manually renamed it to DS2 then you can leave that part unchanged.

"0SALESORG""" should have a comma before the double quotes.

It should end up being something like this :

Application.Run("SAPSetFilter","DS_2","0SALESORG","","INPUT_STRING")

[Former Member]

HI,

Thanks for your reply. i tried your method but unfortunately its not working as expected

created button and inside button i wrote code.

could you please help me i am struggling a lot 😞

[reganmacdonald]

Hi Varun,

No you don't need to use arrays, but if you can it removes the need to write any info out to a sheet first (and then having to re-read it in to clear it).

Arrays however can be tricky to understand if you haven't used them before.

SAPListOfEffectiveFilters is only designed to give you a list of the Filters that are being used. It won't give you any other filters that are not being used. It also doesn't do any clearing of the filters.

Clearing a filter is done with this command :

Application.Run("SAPSetFilter",<filter datasource>,<filter technical name>,"","INPUT_STRING")

The "" near the end is what replaces the current filter contents with a blank.

The issue you have if you get the list of effective filters and display it on a sheet (your screenshot), the clearing is only done with the technical name of the Filter, not its cosmetic name. SAPListOfEffectiveFilters only gives you a cosmetic name.

To find the technical name you can use SAPListOfDimensions. That will give you cosmetic and technical names and you can use that list of find what technical name you should be using.

Application.Run("SAPListOfDimensions", <datasource>)

Cheers,

Regan

[Former Member]

Hi Regan,

Thanks for your comment . but really confused in your blog do we really need to use arrays

could you please explain me more

i tried capture all effective filters by using infofield and try to clear filters by below code. but i am really confused could you please help with your expertise

Private Sub clear_Click()
Application.Run("SAPListOfEffectiveFilters" "DS_1", ,"", "ALL")
End Sub

i applied filter using

in dropdown it was showing only applied filter as below

i want to clear all filters and load values .could you please help me with this

Thanks,

Varun

[Former Member]

Hi Regan,

Thanks for your comment . but really confused in your blog do we really need to use arrays

could you please explain me more

i tried capture all effective filters by using infofield and try to clear filters by below code. but i am really confused could you please help with your expertise

Private Sub clear_Click()
Application.Run("SAPListOfEffectiveFilters" "DS_1", ,"", "ALL")
End Sub

i applied filter using

in dropdown it was showing only applied filter as below

i want to clear all filters and load values .could you please help me with this

Thanks,

Varun

[reganmacdonald]

Hi Varun,

Try my recent blog post here : https://blogs.sap.com/2017/02/03/analysis-for-office-variables-and-filters-via-vba/

It has a method on clearing out any user chosen filters in AO.

Look specifically at "Fourth Step - Cleaning out previous setting before using new ones"

Regards,

Regan