[Solved] Run standard filter in two column in one go using macro
-
- Posts: 19
- Joined: Fri Jun 17, 2022 9:45 am
[Solved] Run standard filter in two column in one go using macro
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
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
Reason: Tagged ✓ [Solved] -- MrProgrammer, forum moderator
OpenOffice 4.0 on Windows 10
Re: Run standard filter in two column in one go using macro
Save our time and provide directly this document and its macro.Lovepreet323 wrote: ↑Fri Jun 17, 2022 10:35 am have an openoffice file in which i used macro to filter on two columns.
Co-admin french forum branch
-
- Posts: 19
- Joined: Fri Jun 17, 2022 9:45 am
Re: Run standard filter in two column in one go using macro
Ods file with micro attached.Please help
OpenOffice 4.0 on Windows 10
Re: Run standard filter in two column in one go using macro
Sorry but you attach an xlsx:
Co-admin french forum branch
- MrProgrammer
- Moderator
- Posts: 4895
- Joined: Fri Jun 04, 2010 7:57 pm
- Location: Wisconsin, USA
Re: Run standard filter in two column in one go using macro
Hi, and welcome to the forum. I ignored your Microsnot-format attachments.
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.
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.
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.Ten Concepts wrote:All of the cells in a row must be the same height
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).
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).
-
- Posts: 19
- Joined: Fri Jun 17, 2022 9:45 am
Re: Run standard filter in two column in one go using macro
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
Macro code txt file and ods with micro attached here with
OpenOffice 4.0 on Windows 10
-
- Posts: 19
- Joined: Fri Jun 17, 2022 9:45 am
Re: Run standard filter in two column in one go using macro
Please provide macro code if any related to this
OpenOffice 4.0 on Windows 10
Re: Run standard filter in two column in one go using macro
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
-
- Posts: 19
- Joined: Fri Jun 17, 2022 9:45 am
Re: Run standard filter in two column in one go using macro
Will anyone help to modify attached macro to filter on two columns on one go
OpenOffice 4.0 on Windows 10
Re: Run standard filter in two column in one go using macro
Where is the problem?
Please give us more details, we don't have any crystal ball to guess what is wrong.Co-admin french forum branch
-
- Posts: 19
- Joined: Fri Jun 17, 2022 9:45 am
Re: Run standard filter in two column in one go using macro
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.
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
Re: Run standard filter in two column in one go using macro
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
ms777
-
- Posts: 19
- Joined: Fri Jun 17, 2022 9:45 am
Re: Solved-Run standard filter in two column in one go using macro
Thanks a lot ms777.Issue solved.You did a great job
OpenOffice 4.0 on Windows 10
-
- Posts: 19
- Joined: Fri Jun 17, 2022 9:45 am
Re: [Solved] Run standard filter in two column in one go using macro
There is one issue now that's macro taking 12-15 sec to run.Please check
OpenOffice 4.0 on Windows 10
- MrProgrammer
- Moderator
- Posts: 4895
- Joined: Fri Jun 04, 2010 7:57 pm
- Location: Wisconsin, USA
Re: [Solved] Run standard filter in two column in one go using macro
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).
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).