[Solved] Macro for hiding rows in a spreadsheet

[Bdelude89]

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:

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

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?

[Lupp]

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:

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

[Bdelude89]

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!