This may be another manifestation of Bug ID 77459.

A BASIC macro is adding (n) rows to a spreadsheet beginning at the row indicated by a selected cell (RefRow).  The contents of the selected row are copied to the 'n' new rows then the values deleted, leaving formats & formulae.

As previously written for OpenOffice V2, the code added the rows so the originally selected row was moved down. The contents were then copied from the selected row (now in its new position further down the spreadsheet) into the added rows, and the values deleted leaving formats & formulae. That worked fine until recently because, it turns out, named ranges of data were always defined globally.

However a problem arises if rows are added within a named range of data AND the scope of the named range is confined to the sheet. Suppose we have a named range Fred = $A$2:$A$100 and we add ten rows somewhere in the middle of that range. Fred should be incremented to $A$2:$A$110 but instead it's decremented to $A$2:$A$90. If the scope is global everything is fine.

The problem lies in the code which copies the selected row to the added rows - around "oActSheet.copyRange (CellAddress, CellRangeAddress)". The selected row must be copied forward to higher-numbered rows rather than backwards to lower-numbered rows.  Code with & without the bug is shown below.

THIS DEMONSTRATES THE PROBLEM:

             CellRangeAddress.Sheet = SheetNbr
             CellRangeAddress.StartColumn = 0
             CellRangeAddress.EndColumn = EndColumn
             CellRangeAddress.StartRow = RefRow
             CellRangeAddress.EndRow = RefRow+n-1
             oActSheet.insertCells (CellRangeAddress, com.sun.star.sheet.CellInsertMode.ROWS)
    '
    '        Copy the the row originally selected from column-0 to 'EndColumn' to each of the inserted rows,
    '        then delete all values, date-time values, and strings.
    '
             CellRangeAddress.StartRow = RefRow+n
             CellRangeAddress.EndRow = RefRow+n
             CellAddress.Sheet = SheetNbr
             CellAddress.Column = 0
             For i = RefRow To RefRow+n-1
                CellAddress.Row = i
                oActSheet.copyRange (CellAddress, CellRangeAddress)
             Next i
             Flags = com.sun.star.sheet.CellFlags.VALUE + _
                     com.sun.star.sheet.CellFlags.DATETIME + _
                     com.sun.star.sheet.CellFlags.STRING + _
                     com.sun.star.sheet.CellFlags.ANNOTATION
             oActSheet.getCellRangeByPosition(0,RefRow,EndColumn,RefRow+n-1).clearContents(Flags)


BUT THIS WORKS FINE

    oCellRangeAddress.Sheet = SheetNbr
    oCellRangeAddress.StartColumn = 0
    oCellRangeAddress.EndColumn = EndColumn
    oCellRangeAddress.StartRow = RefRow+1
    oCellRangeAddress.EndRow = RefRow+n
    oActSheet.insertCells (oCellRangeAddress, com.sun.star.sheet.CellInsertMode.ROWS)
    '
    oCellRangeAddress.StartRow = RefRow
    oCellRangeAddress.EndRow = RefRow
    oCellAddress.Sheet = SheetNbr
    oCellAddress.Column = 0
    For i = RefRow+1 To RefRow+n
       oCellAddress.Row = i
       oActSheet.copyRange (oCellAddress, oCellRangeAddress)
       Next i
    '
    Flags = com.sun.star.sheet.CellFlags.VALUE + _
            com.sun.star.sheet.CellFlags.DATETIME + _
            com.sun.star.sheet.CellFlags.STRING + _
            com.sun.star.sheet.CellFlags.ANNOTATION
    oActSheet.getCellRangeByPosition (0,RefRow,EndColumn,RefRow+n-1).clearContents(Flags)


Thanks & Regards,
David Lochrin
dlochrin@d2.net.au