I'm just confused on how to write it so that it loops through each cell in column A to the end of the spreadsheet?Sub FTest()
theSheet=thisComponent.getSheets.getByName( "Production" )
CellDest = theSheet.getCellByPosition(0,0)
If CellDest.string = "1" then CellDest.Rows.IsVisible=false
end if
end sub
[Solved] Macro for hiding rows in a spreadsheet
[Solved] Macro for hiding rows in a spreadsheet
I need to write a macro that will hide a row if a cell in column A has a "1". Simple enough, I have the basic code here:
Last edited by Bdelude89 on Wed Mar 28, 2018 3:45 pm, edited 1 time in total.
Openoffice 3 on Windows 10
Re: Macro for hiding rows in a spreadsheet, help?
What you want is filtering with the operator "NOT_EQUAL".
Why don't you simply use a filter from the UI: 'Data', then 'Autofilter' or 'More Filters' ... ?
Under no circumstances you should loop through 2^20 cells one by one.
If you urgently need to apply a filter from a running Sub see this example:
Why don't you simply use a filter from the UI: 'Data', then 'Autofilter' or 'More Filters' ... ?
Under no circumstances you should loop through 2^20 cells one by one.
If you urgently need to apply a filter from a running Sub see this example:
Code: Select all
Sub myFilter(Optional pZ as Long, Optional pX As Long, Optional pContent As Variant)
REM See: Andrew Pitonyak; OpenOffice.org Macros Explained
If IsMissing(pZ) Then pZ = 1
If IsMissing(pX) Then pX = 1
If IsMissing(pContent) Then pContent = 1
Dim hFields(0) As New com.sun.star.sheet.TableFilterField
doc0 = ThisComponent
theSheet = doc0.Sheets(pZ - 1)
theRange = theSheet.GetCellRangeByPosition(pX - 1, 0, pX - 1, theSheet.RangeAddress.EndRow)
hFilterDesc = theSheet.CreateFilterDescriptor(True)
With hFields(0)
.Field = theRange.RangeAddress.StartColumn
If TypeName(pContent)="String" Then
.IsNumeric = False
.StringValue = pContent
Else
.IsNumeric = True
.NumericValue = pContent
End If
.Operator = com.sun.star.sheet.FilterOperator.NOT_EQUAL
End With
hFilterDesc.SetFilterFields(hFields())
hFilterDesc.ContainsHeader = False
theSheet.Filter(hFilterDesc)
End Sub
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
---
Lupp from München
Re: Macro for hiding rows in a spreadsheet, help?
My sheet is a table of restaurant inventory items and amounts for them that need to be prepared. It populates from another sheet, and has blank spaces in it to allow it to expand when needed.
These sheets need to be printed, however, and printing the blank spaces as well would make it extraordinarily long. At print time I need to be able to compress the sheets down. So in column A I have a formula that determines if that row can be hidden and displays a 1 if it can. The sheet itself is about 1080 rows long.
This solution works really well, thank you!
These sheets need to be printed, however, and printing the blank spaces as well would make it extraordinarily long. At print time I need to be able to compress the sheets down. So in column A I have a formula that determines if that row can be hidden and displays a 1 if it can. The sheet itself is about 1080 rows long.
This solution works really well, thank you!
Openoffice 3 on Windows 10