Help to create a Filter & Restore Sheet macro's

[rde]

I have just migrated to Calc and I have spend days - without success, trying to create 2 filter macro’s in Calc that I used to use in Excel. I am hoping that someone can help me with this.

I have a table with a range of B9:AAZ5000 (row 9 is the header row).
However, for this filter task only text in the range D10:D5000 is searched - based on the text search criteria in cell D6.

What each macro needs to do:

Filter Macro

1. Clear cell D6
2. Paste the clipboard to cell D6 (D6 being the Search Criteria)
3. Filter the range D10:D5000 based the cell D6 Search Criteria (this results in only rows with matching text in D6 and range D10:D5000 being displayed)

Restore Sheet

1. Clear the search criteria in cell D6
2. Clear the filter and/or
3. Unhide/Show the hidden rows caused by the filter macro above
4. Return the cursor to Cell A10

I am really hoping someone can help me with these macro, as I am getting no where on my own.
Thank you in advance

[Villeroy]

ask132662.ods (60.2 KB)

[sokol92]

See also this topic.

[rde]

Hi Gilberto, thank you for your solution. I really appreciate your time.
I have applied your macro’s to my spreadsheet and it functions as per your example.
Here is what I have noted from your Macro’s

1. The Filter and Clear macros row by row search is very slow, about .5 sec per row. So over 5000 rows it takes each about 3 minutes to filter. This is not practical if 20 or 30 searches ned to be done.
   The type of behaviour I am hoping for is like the speed of the Standard Filter, which is an instant filter. Is it possible to create a macro that functions like the Standard Filter?

2. The HideRowsColumnD macro doesn’t include the first step of Pasting the Clipboard into cell D6

3. The ShowLinesClear macro doesn’t include the step of Clearing cell D6 and the last step of returning the cursor to cell A10. I think that this can be achieved with

Sub DeleteD6
ThisComponent.Sheets(0).getCellRangeByName(“D6”).setString("")
End Sub

and (a recorded Macro)

sub CursorA10
rem ----------------------------------------------------------------------
rem define variables
dim document as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService(“com.sun.star.frame.DispatchHelper”)

rem ----------------------------------------------------------------------
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = “ToPoint”
args1(0).Value = “$Orders.$A$10”

dispatcher.executeDispatch(document, “.uno:GoToCell”, “”, 0, args1())

end sub

I hope that my reply is understandable and that solutions to my observations are possible?

[rde]

Hi Villeroy, thank you for your .ods
I have had look at the spreadsheet. It looks positive for what I am trying to achieve.
I will try and study what you have created to see if I can use this in my spreadsheet.
I will let you know how I get on
Thank you

[schiavinatto]

rde_132662.ods (13,0,KB)