Using macro you can clear everything that contains in a cell or in a range of cells. In this tutorial we will clear every type of contents from a range.
Lets define a sub which would clear the contents of the range.
Sub ClearRangeContents()
End Sub
Lets get a hold of the range in Sheet1. The function getCellRangeByName
returns a range object corresponsing to the range that is passed via argument. Read Range Processing using Macro in LibreOffice Calc – Part 1 for range processing basics.
Dim oDoc As Object
Dim oSheet As Object
Dim oCell As Object
Dim oRange As Object
oDoc = ThisComponent
oSheet = oDoc.Sheets(0) ' Refers to Sheet1 as in 0, 1, 2 etc
oRange = oSheet.getCellRangeByName("A3:B10")

The range function clearContents(flags)
can be used to clear the contents of the range. The flags are nothing but the types of values that can resides in a cell. It can be string, numerics, formulas, styles etc. You can specify which types you want to clear from that range. ClearContents argument flags is of long
data type and it can takes multiple flags as input.
The flags are listed on OpenOffice/LibreOffice constant group CellFlags which is part of com.sun.star.sheet
.
List of Flags under com.sun.star.sheet.CellFlags
Flags | Description | Values |
VALUE | selects constant numeric values that are not formatted as dates or times. | 1 |
DATETIME | selects constant numeric values that have a date or time number format. | 2 |
STRING | selects constant strings. | 4 |
ANNOTATION | selects cell annotations. | 8 |
FORMULA | selects formulas. | 16 |
HARDATTR | selects all explicit formatting, but not the formatting which is applied implicitly through style sheets. | 32 |
STYLES | selects cell styles. | 64 |
OBJECTS | selects drawing objects. | 128 |
EDITATTR | selects formatting within parts of the cell contents. | 256 |
FORMATTED | selects cells with formatting within the cells or cells with more than one paragraph within the cells. | 512 |
Put all these constants under a variable and pass it on to clearContents function.
Dim oFlags As Long
oFlags = com.sun.star.sheet.CellFlags.VALUE + _
com.sun.star.sheet.CellFlags.DATETIME + _
com.sun.star.sheet.CellFlags.STRING + _
com.sun.star.sheet.CellFlags.ANNOTATION + _
com.sun.star.sheet.CellFlags.FORMULA + _
com.sun.star.sheet.CellFlags.HARDATTR + _
com.sun.star.sheet.CellFlags.STYLES + _
com.sun.star.sheet.CellFlags.OBJECTS + _
com.sun.star.sheet.CellFlags.EDITATTR
oRange.clearContents(oFlags)
Run
Put below entire code block in a Calc spreadsheet containing various values in a range and run the macro by calling the function. You may see the output is cleared of all values, formatting etc.
Complete Macro
Sub ClearRangeContents()
Dim oDoc As Object
Dim oSheet As Object
Dim oCell As Object
Dim oRange As Object
Dim oFlags As Long
oDoc = ThisComponent
oSheet = oDoc.Sheets(0) ' Refers to Sheet1 as in 0, 1, 2 etc
oRange = oSheet.getCellRangeByName("A3:B10")
oFlags = com.sun.star.sheet.CellFlags.VALUE + _
com.sun.star.sheet.CellFlags.DATETIME + _
com.sun.star.sheet.CellFlags.STRING + _
com.sun.star.sheet.CellFlags.ANNOTATION + _
com.sun.star.sheet.CellFlags.FORMULA + _
com.sun.star.sheet.CellFlags.HARDATTR + _
com.sun.star.sheet.CellFlags.STYLES + _
com.sun.star.sheet.CellFlags.OBJECTS + _
com.sun.star.sheet.CellFlags.EDITATTR
oRange.clearContents(oFlags)
End Sub
Looking for Something Else?
If you are looking for something else in LibreOffice macro tutorials, Or, wants to learn more about it, please follow below link for complete Macro Tutorials Index:
LibreOffice Macro Tutorial Index
We bring the latest tech, software news and stuff that matters. Stay in touch via Telegram, Twitter, YouTube, and Facebook and never miss an update!
