Russell King wrote:
Hi,
I am trying to build a macro that will build a synopsis sheet from the
other sheets in a spreadsheet. The synopsis is over 1600 rows long, so
it is a bit tedious to do it by hand and since each sheet changes not
just the data, but the amount of data, the macro ends up putting in a
lot of references to cells that are empty in order to make sure it
gets it all. The macro then sorts the data which leaves over 600 rows
of zeros at the top. What I need is a function for my macro that will
go through the top of the sheet, leaving the header row alone, and
delete the rows that have a value (not content) of zero.
I have tried various code examples (ie, getCellByPosition), but it
doesn't work (unexpected syntax errors)
All I want to do is dump the value of a cell (ie $A$2) into a string
variable so that I can compare it. Why does it have to be so hard?
rk
The following macro searches the first column and removes all rows that
contain a zero value. You can search a different column by changing the
lLeft and lRight values shown below.
Sub RemoveZeroRows
Dim oCell
Dim oSheet
Dim oCursor
Dim oData()
Dim oRow()
Dim oRows
Dim n As Long
oSheet = ThisComponent.getSheets().getByIndex(0)
REM Start with cell A2... Skip the first row
oCell = oSheet.GetCellbyPosition( 0, 1 )
oCursor = oSheet.createCursorByRange(oCell)
oCursor.GotoEndOfUsedArea(True)
REM If there is a lot of data, then perhaps I can get the
REM last used row and use that to inspect only a single column.
REM This should be faster and use less memory.
Dim oRange
Dim lLeft As Long : lLeft = 0
Dim lTop As Long : lTop = 1
Dim lRight As Long : lRight = 0
Dim lBottom As Long : lBottom = oCursor.RangeAddress.EndRow
oRows = oSheet.getRows()
REM Get the data for column A, excluding the first row (becuase lTop is 1)
oRange = oSheet.getCellRangeByPosition(lLeft, lTop, lRight, lBottom)
oData() = oRange.getDataArray()
REM Move through the list BACKWARDS. This keeps the rows in synch
REM with the data array.
For n = UBound(oData()) To LBound(oData()) Step -1
oRow() = oData(n)
If oRow(0) = 0 Then
oRows.removeByIndex(n + lTop, 1)
End If
Next
End Sub
--
Andrew Pitonyak
My Macro Document: http://www.pitonyak.org/AndrewMacro.odt
My Book: http://www.hentzenwerke.com/catalog/oome.htm
Info: http://www.pitonyak.org/oo.php
See Also: http://documentation.openoffice.org/HOW_TO/index.html
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
