Skip to Content
0

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

Feb 03, 2017 at 03:12 PM

197

avatar image

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
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

6 Answers

Regan MacDonald Feb 04, 2017 at 08:55 AM
0

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

Share
10 |10000 characters needed characters left characters exceeded
varun garaga Feb 05, 2017 at 10:25 AM
0

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

Share
10 |10000 characters needed characters left characters exceeded
varun garaga Feb 05, 2017 at 10:25 AM
0

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


e8exu.png (12.6 kB)
Share
10 |10000 characters needed characters left characters exceeded
Regan MacDonald Feb 06, 2017 at 12:46 AM
0

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

Share
10 |10000 characters needed characters left characters exceeded
varun garaga Feb 09, 2017 at 07:41 PM
0

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 :(


opuqc.png (3.6 kB)
6otnm.png (737 B)
Share
10 |10000 characters needed characters left characters exceeded
Regan MacDonald Feb 19, 2017 at 03:46 AM
0

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")
Share
10 |10000 characters needed characters left characters exceeded