Macros/Basic/Calc/Ranges

TDF LibreOffice Document Liberation Project Community Blogs Weblate Nextcloud Redmine Ask LibreOffice Donate

• Wiki Home
• Development
• Design
• QA
• Events
• Documentation
• Website
• Localization
• Accessibility
• Marketing
• Diversity
• Wiki Help

• Macros
• General Macros
• Macros for Writer
• Macros for Calc
• Macros for Impress
• Macros for Draw
• Macros for Base
• Macros for Math
• Basic/Python

Return to Index

Ranges

Get cell by name

sheet = ThisComponent.CurrentController.ActiveSheet
    cell = sheet.getCellRangeByName("A1")

Get cell by position

sheet = ThisComponent.CurrentController.ActiveSheet

    'Cell C6
    column = 2
    row = 5
    cell = sheet.getCellByPosition(column, row)

Get range by name

sheet = ThisComponent.CurrentController.ActiveSheet
    range = sheet.getCellRangeByName("B5:D10")

Get range by position

sheet = ThisComponent.CurrentController.ActiveSheet

    'Range B5:D10
    start_column = 1
    start_row = 4
    end_column = 3
    end_row = 9
    range = sheet.getCellRangeByPosition(start_column, start_row, end_column, end_row)

Get address like string

sheet = ThisComponent.CurrentController.ActiveSheet

    cell = sheet.getCellRangeByName("B2")
    range = sheet.getCellRangeByName("B2:C5")

    Msgbox cell.AbsoluteName
    Msgbox range.AbsoluteName

Get address like struct

• Only for cells

sheet = ThisComponent.CurrentController.ActiveSheet

    cell = sheet.getCellRangeByName("B2")
    address = cell.CellAddress

    Msgbox address.Column
    Msgbox address.Row

• For ranges

sheet = ThisComponent.CurrentController.ActiveSheet

    range = sheet.getCellRangeByName("B2:C5")
    address = range.RangeAddress

    Msgbox address.StartColumn
    Msgbox address.StartRow
    Msgbox address.EndColumn
    Msgbox address.EndRow

Group of ranges

• Create and add ranges

doc = ThisComponent
    sheet = doc.CurrentController.ActiveSheet
    group = doc.createInstance("com.sun.star.sheet.SheetCellRanges")

    range1 = sheet.getCellRangeByName("A1:B2")
    range2 = sheet.getCellRangeByName("D5:F10")
    range3 = sheet.getCellRangeByName("H15:J20")

    group.addRangeAddress(range1.RangeAddress, False)
    group.addRangeAddress(range2.RangeAddress, False)
    group.addRangeAddress(range3.RangeAddress, False)

    MsgBox group.RangeAddressesAsString

• We can use an array

ranges = Array(range1.RangeAddress, range2.RangeAddress, range3.RangeAddress)
    group.addRangeAddresses(ranges, False)

• Remove

group.removeRangeAddress(range2.RangeAddress)

Columns

• Count

sheet = ThisComponent.CurrentController.ActiveSheet
    range = sheet.getCellRangeByName("D5:F10")

    MsgBox range.Columns.Count

• Get by index

MsgBox range.Columns(0).Name

• Get by name

MsgBox range.Columns("E").Name

Rows

• Count

sheet = ThisComponent.CurrentController.ActiveSheet
    range = sheet.getCellRangeByName("D5:F10")

    MsgBox range.Rows.Count

• Get by index

MsgBox range.Rows(1).AbsoluteName

Current selection

selection = ThisComponent.CurrentSelection
    For Each service In selection.SupportedServiceNames
        MsgBox service
    Next service

CAUTION: Current selection can be many things. Use supportsService method to disambiguate.

If select is	Supported service name
Cell	com.sun.star.sheet.SheetCell
Range	com.sun.star.sheet.SheetCellRange
Ranges	com.sun.star.sheet.SheetCellRanges
Columns	com.sun.star.table.TableColumns
Rows	com.sun.star.table.TableRows
Shape	com.sun.star.drawing.ShapeCollection

Ranges with name

• Manage names Ctrl+F3

sheet = ThisComponent.CurrentController.ActiveSheet
    range = sheet.getCellRangeByName("Address")

    MsgBox range.AbsoluteName

• Count

names = ThisComponent.NamedRanges
    MsgBox names.Count

• Get by index

range = names(0)
    MsgBox range.Name

• Get by name

range = names("Address")
    MsgBox range.Name

• Add new

sheet = ThisComponent.CurrentController.ActiveSheet
    cell = sheet.getCellRangeByName("A1")
    names = ThisComponent.NamedRanges
    flag = 0
    names.addNewByName("NewData", "$A$1:$B$7", cell.CellAddress, flag)

For flags look: NamedRangeFlag

• Remove

names.removeByName("NewData")

• Export list

names.outputList(cell.CellAddress)

Cursors

• Create by range

sheet = ThisComponent.CurrentController.ActiveSheet
    cell = sheet.getCellRangeByName("A1")

    cursor = sheet.createCursorByRange(cell)
    MsgBox cursor.ImplementationName

• Move to next cell

cursor.gotoNext()

• Move to previous cell

cursor.gotoPrevious()

• Move to (Columns, Rows)

cursor.gotoOffset(2, 5)

• Move to start

cursor.gotoStart()

• Move to end

cursor.gotoEnd()

• Expand to current region

cursor.collapseToCurrentRegion()

Tip: Get last used row in range

cursor.gotoEnd()
    MsgBox cursor.RangeAddress.EndRow

    'Or

    cursor.collapseToCurrentRegion()
    MsgBox cursor.RangeAddress.EndRow

• Expand to current array

cursor.collapseToCurrentArray()

• Expand to merged area

cursor.collapseToMergedArea()

• Expand to entire columns

cursor.expandToEntireColumns()

• Expand to entire rows

cursor.expandToEntireRows()

• Change size of range

columns = 2
    rows = 5
    cursor.collapseToSize(columns, rows)

• Move to start of used area

cursor.gotoStartOfUsedArea(False)

• Move to end of used area

cursor.gotoEndOfUsedArea(False)

Argument expand (True) or not (False) the range selection.

Special selections

Tip: All next methods, always get a group range (ScCellRangesObj)

• Get visible cells

selection = ThisComponent.CurrentSelection
    ranges = selection.queryVisibleCells()

• Get empty cells

selection = ThisComponent.CurrentSelection
    ranges = selection.queryEmptyCells()

• Get cells with text

selection = ThisComponent.CurrentSelection
    ranges = selection.queryContentCells(com.sun.star.sheet.CellFlags.STRING)

Look flag options: CellFlags

Tip: You can sum two or more values.

• Get cells with error in formula

selection = ThisComponent.CurrentSelection
    ranges = selection.queryFormulaCells(com.sun.star.sheet.FormulaResult.ERROR)

Look flag options: FormulaResult

• Get values differents by column.

selection = ThisComponent.CurrentSelection
    source = selection.getCellByPosition(0, 0)
    ranges = selection.queryColumnDifferences(source.CellAddress)

• Get values differents by rows.

selection = ThisComponent.CurrentSelection
    source = selection.getCellByPosition(0, 0)
    ranges = selection.queryRowDifferences(source.CellAddress)

• Get intersection cells

sheet = ThisComponent.CurrentController.ActiveSheet
    range1 = sheet.getCellRangeByName("B2:I11")
    range2 = sheet.getCellRangeByName("G8:N17")

    ranges = rango1.queryIntersection(rango2.RangeAddress)

Working with ranges

Copy

sheet = ThisComponent.CurrentController.ActiveSheet
    source = sheet.getCellRangeByName("A1:B3")
    target = sheet.getCellRangeByName("D10")

    sheet.copyRange(target.CellAddress, source.RangeAddress)

You can copy columns or rows, but make sure that the cell target it's the first cell.

source_columns = sheet.Columns(2)
    source_rows = sheet.Rows(1)

    target_column = sheet.getCellRangeByName("H1")  'First row
    target_row = sheet.getCellRangeByName("A20")    'First column

Move

sheet = ThisComponent.CurrentController.ActiveSheet
    source = sheet.getCellRangeByName("A1:B3")
    target = sheet.getCellRangeByName("D10")

    sheet.moveRange(target.CellAddress, source.RangeAddress)

Insert

• Insert and cells down

sheet = ThisComponent.CurrentController.ActiveSheet
    target = CreateUnoStruct("com.sun.star.table.CellRangeAddress")

    With target
        .Sheet = sheet.RangeAddress.Sheet
        .StartColumn = 1
        .StartRow = 1
        .EndColumn = 3
        .EndRow = 3
    End With

    sheet.insertCells(target, com.sun.star.sheet.CellInsertMode.DOWN)

For other options look: CellInsertMode

• Insert columns

sheet = ThisComponent.CurrentController.ActiveSheet
    start = 4
    columns = 2
    sheet.Columns.insertByIndex(start, columns)

• Insert rows

sheet = ThisComponent.CurrentController.ActiveSheet
    start = 2
    rows = 5
    sheet.Rows.insertByIndex(start, rows)

Delete

• Delete and move cells up

sheet = ThisComponent.CurrentController.ActiveSheet
    selection = ThisComponent.CurrentSelection

    sheet.removeRange(selection.RangeAddress, com.sun.star.sheet.CellDeleteMode.UP)

For other options look: CellDeleteMode

• Delete columns

sheet = ThisComponent.CurrentController.ActiveSheet
    start = 0
    columns = 2
    sheet.Columns.removeByIndex(start, columns)

• Delete rows

sheet = ThisComponent.CurrentController.ActiveSheet
    start = 9
    rows = 3
    sheet.Rows.removeByIndex(start, rows)