[Solved] Run standard filter in two column in one go using macro

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
Lovepreet323
Posts: 19
Joined: Fri Jun 17, 2022 9:45 am

[Solved] Run standard filter in two column in one go using macro

Post by Lovepreet323 »

Dear Team
I have an openoffice file in which i used macro to filter on two columns. But when i run this macro it filter column 6 and then column 8. Filtered data in column 6 override by filter on column 8. Please help to get filtered data in one go from both column 6 "G"and 8 "I". Want to filter word "ADD05021" from both column
Macro code txt file and ods with micro attached here with
Attachments
LSDOCS.ods
Ods file uploaded.please check
(15.28 KiB) Downloaded 81 times
LSDOCS.xlsx
(126.94 KiB) Downloaded 75 times
SimpleSheetFilter_2 MACRO CODE.docx
(12.73 KiB) Downloaded 79 times
Last edited by MrProgrammer on Tue Jun 21, 2022 8:30 pm, edited 5 times in total.
Reason: Tagged ✓ [Solved] -- MrProgrammer, forum moderator
OpenOffice 4.0 on Windows 10
Bidouille
Volunteer
Posts: 574
Joined: Mon Nov 19, 2007 10:58 am
Location: France

Re: Run standard filter in two column in one go using macro

Post by Bidouille »

Lovepreet323 wrote: Fri Jun 17, 2022 10:35 am have an openoffice file in which i used macro to filter on two columns.
Save our time and provide directly this document and its macro.
Lovepreet323
Posts: 19
Joined: Fri Jun 17, 2022 9:45 am

Re: Run standard filter in two column in one go using macro

Post by Lovepreet323 »

Ods file with micro attached.Please help
OpenOffice 4.0 on Windows 10
Bidouille
Volunteer
Posts: 574
Joined: Mon Nov 19, 2007 10:58 am
Location: France

Re: Run standard filter in two column in one go using macro

Post by Bidouille »

Lovepreet323 wrote: Fri Jun 17, 2022 3:14 pm Ods file with micro attached
Sorry but you attach an xlsx:
capture.png
capture.png (27.3 KiB) Viewed 2691 times
User avatar
MrProgrammer
Moderator
Posts: 4894
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Run standard filter in two column in one go using macro

Post by MrProgrammer »

Hi, and welcome to the forum. I ignored your Microsnot-format attachments.

Lovepreet323 wrote: Fri Jun 17, 2022 10:35 am column 6 and then column 8
Rows have numbers; columns have letters. I will presume column 6 means column F and column 8 means column H. Read section 0. Principal Components in Ten concepts that every Calc user should know.

Lovepreet323 wrote: Fri Jun 17, 2022 10:35 am Filtered data in column 6 override by filter on column 8.
Ten Concepts wrote:All of the cells in a row must be the same height
You have a fundamental problem. A filter shows or hides rows in the spreadsheet. Hiding a row sets its display height to zero. A row is either completely shown or completely hidden. It is not possible to show some cells in a row and hide others in that row. Filtering on F sets the show/hide status for all the rows in the data range, including rows formerly shown/hidden by some different filter.

Lovepreet323 wrote: Fri Jun 17, 2022 10:35 am Want to filter word "ADD05021" from both column
A. You could create a filter which shows only rows with:   "ADD05021" in column F and "ADD05021" in column H.
B. You could create a filter which shows only rows with:   "ADD05021" in column F  or  "ADD05021" in column H.
C. You could create conditional formatting which highlights "ADD05021" in columns F and H.
I can show you how to do A, B or C with the standard Calc user interface. I do not offer to write macros for people.
I don't understand "both column" but no filter can combine data from two columns and display it in one column.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the subject field. Select the green checkmark icon at the same time.
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel.   The locale for any menus or Calc formulas in my posts is English (USA).
Lovepreet323
Posts: 19
Joined: Fri Jun 17, 2022 9:45 am

Re: Run standard filter in two column in one go using macro

Post by Lovepreet323 »

Please help to get filtered data in one go from both column 6 "G"and 8 "I". Want to filter word "ADD05021" from both column
Macro code txt file and ods with micro attached here with
OpenOffice 4.0 on Windows 10
Lovepreet323
Posts: 19
Joined: Fri Jun 17, 2022 9:45 am

Re: Run standard filter in two column in one go using macro

Post by Lovepreet323 »

Please provide macro code if any related to this
OpenOffice 4.0 on Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Run standard filter in two column in one go using macro

Post by Villeroy »

No, I certainly won't code for you.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Lovepreet323
Posts: 19
Joined: Fri Jun 17, 2022 9:45 am

Re: Run standard filter in two column in one go using macro

Post by Lovepreet323 »

Will anyone help to modify attached macro to filter on two columns on one go
OpenOffice 4.0 on Windows 10
Bidouille
Volunteer
Posts: 574
Joined: Mon Nov 19, 2007 10:58 am
Location: France

Re: Run standard filter in two column in one go using macro

Post by Bidouille »

Where is the problem?
capture.png
capture.png (107.58 KiB) Viewed 2432 times
Please give us more details, we don't have any crystal ball to guess what is wrong.
Lovepreet323
Posts: 19
Joined: Fri Jun 17, 2022 9:45 am

Re: Run standard filter in two column in one go using macro

Post by Lovepreet323 »

There are two filter in macro.one filter on column g "ENTBY" and another filter on column i "MODBY".
issue is this macro cannot filter both columns at same time and not showing combined filtered data of both column.
It first show filtered data on column g and then shows filtered data on column i. You can check this by running macro and then undo on sheet.
OpenOffice 4.0 on Windows 10
ms777
Volunteer
Posts: 177
Joined: Mon Oct 08, 2007 1:33 am

Re: Run standard filter in two column in one go using macro

Post by ms777 »

Code: Select all

Sub SimpleSheetFilter_2()
  Dim oSheet          ' Sheet to filter.
  Dim oRange          ' Range to be filtered.
  Dim oFilterDesc as object   ' Filter descriptor.
  Dim oFields(1) As New com.sun.star.sheet.TableFilterField
 
  oSheet = ThisComponent.getSheets().getByIndex(0)
  oRange = oSheet.getCellRangeByName("A1:L5000")
   
  REM If argument is True, creates an
  REM empty filter descriptor.
  oFilterDesc = oRange.createFilterDescriptor(true)
  'xray oFilterDesc
   oSheet.filter(oFilterDesc)
 
  REM Setup a field to view cells with content that
  REM start with the letter ADD05021.
  With oFields(0)  
'    .Connection = com.sun.star.sheet.FilterConnection.OR  
    .Field = 6             ' Filter column G.
    .IsNumeric = False      ' Use a string, not a number.
    .StringValue = "ADD05021"   
    .Operator = com.sun.star.sheet.FilterOperator.EQUAL
  End With
  REM Setup a field that requires both conditions and
  REM this new condition requires a value greater or
  REM equal to ADD05021.
  With oFields(1)
    .Connection = com.sun.star.sheet.FilterConnection.OR
    .Field = 8              ' Filter column I.
    .IsNumeric = false       ' Use a string, not a number.
    .StringValue = "ADD05021"    
    .Operator = com.sun.star.sheet.FilterOperator.EQUAL
  End With
 
  oFilterDesc.setFilterFields(oFields)
  oFilterDesc.ContainsHeader = True
  oFilterDesc.UseRegularExpressions = True
  oSheet.filter(oFilterDesc)
End Sub
You're welcome

ms777
Lovepreet323
Posts: 19
Joined: Fri Jun 17, 2022 9:45 am

Re: Solved-Run standard filter in two column in one go using macro

Post by Lovepreet323 »

Thanks a lot ms777.Issue solved.You did a great job
OpenOffice 4.0 on Windows 10
Lovepreet323
Posts: 19
Joined: Fri Jun 17, 2022 9:45 am

Re: [Solved] Run standard filter in two column in one go using macro

Post by Lovepreet323 »

There is one issue now that's macro taking 12-15 sec to run.Please check
OpenOffice 4.0 on Windows 10
User avatar
MrProgrammer
Moderator
Posts: 4894
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: [Solved] Run standard filter in two column in one go using macro

Post by MrProgrammer »

Lovepreet323 wrote: Wed Jun 22, 2022 2:02 pm Please check
I did. The macro from ms777 always runs in less than a second for me. I added Dim g As Long : g = GETSYSTEMTICKS() at the beginning and MsgBox "Macro took " & GETSYSTEMTICKS()-g & " ticks" at the end. A "tick" on my system is one millisecond. The macro typically runs in about 850 ticks. I suggest you use standard debugging techniques to determine why your system is different. Don't know how to do that? Then this is a skill you'll need to learn if you're going to use macros. Or you can just accept that this is how the macro works for you. I do not offer to assist with macro debugging.
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel.   The locale for any menus or Calc formulas in my posts is English (USA).
Post Reply