Ask Your Question
1

Resetting A Group Of AutoFilters [closed]

asked 2013-03-14 16:22:08 +0200

Janenn gravatar image

updated 2015-10-26 21:46:45 +0200

Alex Kemp gravatar image

Hello,

I use a large spreadsheet with nearly every column set to AutoFilter. Quite often, multiple columns are then sorted by filtering to view desired results.

A macro is needed to reset the entire group of column filters to 'All', returning the sheet to it's pre-filtered state. Can anyone offer a solution?

Thank You!

Janenn (LO 3.6, WIN 7

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2015-10-26 21:47:00.520422

3 Answers

Sort by » oldest newest most voted
1

answered 2013-03-15 17:00:35 +0200

Janenn gravatar image

Thanks to you both for taking the time to respond...

Elegant solutions!

Janenn

edit flag offensive delete link more
1

answered 2013-03-15 00:14:16 +0200

w_whalley gravatar image

You can use a macro to toggle filters on and off. The first step in the macro should be to position the cursor to one of the field names in the filter (e.g. cell $A$1 -- change for your case). You can then hook the macro to a button or a menu for ease of use. To reset, not toggle, merely execute the filter toggle line twice.

sub Toggle_filter
dim document   as object
dim dispatcher as object
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name="ToPoint"
args1(0).Value="$A$1"  rem **** Change for your case ****
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())
rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:DataFilterAutoFilter", "", 0, Array())
rem Uncommenting the following line turns the toggle into a reset
rem dispatcher.executeDispatch(document, ".uno:DataFilterAutoFilter", "", 0, Array())
end sub
edit flag offensive delete link more
1

answered 2013-03-14 22:01:34 +0200

m.a.riosv gravatar image

If I have understood right your question, it is easy to solve:

Select the filter in any of the columns.
Select the standard filter.
Select the first field name.
Go to the top for select none.
Click Ok.
That's all.

image description

edit flag offensive delete link more

Question Tools

Stats

Asked: 2013-03-14 16:22:08 +0200

Seen: 1,101 times

Last updated: Mar 15 '13