Calc basic: deleting row below the one I was expecting

[raretrack]

Hi

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

Rob

[karolus]

edit for better Code-Syntaxhighlight.
Karolus

[mikekaganski]

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.

[raretrack]

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