Ask Your Question

Calc basic: deleting row below the one I was expecting

asked 2019-08-11 18:31:48 +0100

raretrack gravatar image

updated 2019-08-11 20:34:59 +0100

karolus gravatar image


I'm starting to migrate from Excel VBA to LibreOffice Calc Basic. Most of the time I can solve conversion problems by looking at this forum, in both of Andrew Pitonyak's ebooks, or via search engine.

But this one has me a bit stumped. Not so much that I can't work out what's going wrong, but more that I can't fathom why it's wrong!

My code, which includes comments as to the expected and actual behaviour is:

Sub RowDeleteTest()

Dim iRow As Long
Dim oCell As Object
Dim oDoc As Object
Dim oSheet As Object
Dim oSheets As Object

oDoc = ThisComponent
oSheets = oDoc.Sheets
oSheet = oDoc.Sheets.getByName("Sheet1")

'delete rows with x or X in cells J2:J46'
For iRow =  46 to 2 Step -1
  oCell = oSheet.getCellRangeByName("J" & iRow)

  If LCase(oCell.String) = "x" then
    'REM on my sheet cell J46 contains "x", cell J47 is empty'
    'REM the Msgbox will show "About to delete row 46 containing x"'
    Msgbox "About to delete row " & iRow & " containing " & oCell.String 
    oSheet.Rows.removeByIndex(iRow, 1)
    'REM but the macro then deletes row 47, iRow 45 will delete row 46, etc.'
    'REM is the solution as simple as oSheet.Rows.removeByIndex(iRow +1, 1) ?'
  End If
Next iRow

End Sub

None of the examples I've seen when searching about this seem to mention this issue. I don't want to lose any data when I roll this out for real, so really hope I can get to the bottom of this. Is my proposed solution watertight? Or I making a fundamental error here (entirely possible!)?

Many thanks


edit retag flag offensive close merge delete


edit for better Code-Syntaxhighlight.

karolus gravatar imagekarolus ( 2019-08-11 20:35:13 +0100 )edit

1 Answer

Sort by » oldest newest most voted

answered 2019-08-11 18:40:12 +0100

Indices are 0-based, so first row (that has A1, B1, etc.) has index 0. If you put user-visible row numbers into iRow, then you need to pass iRow - 1 to removeByIndex.

edit flag offensive delete link more


Thanks very much for the explanation, Mike. That should save my data, and my sanity!

raretrack gravatar imageraretrack ( 2019-08-11 19:09:01 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-08-11 18:31:48 +0100

Seen: 59 times

Last updated: Aug 11 '19