Deleting All Types of Contents from Calc Range using Macro

This tutorial explains how to delete contents from cells or ranges in LibreOffice Calc using macro.

Using a 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 content from a range. Before clearing out contents from ranges, the macro needs to determine the type of cell contents. Because if you want to delete everything from cells, you need to consider the value, the formatting styles, etc.

Let’s take a look at this using some examples.

Note: This tutorial assumes you know how to create and run a macro. If you are new, you can check out this tutorial first and return to this page.

Delete cell and range contents in LibreOffice using Macro

Let’s define a procedure which would clear the contents of the range.

Sub ClearRangeContents()
End Sub

Let’s get a hold of the range in Sheet1. The function getCellRangeByName returns a range object corresponding 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 reside in a cell. It can be string, numerics, formulas, styles etc. You can specify which types you want to clear from that range. The clearContents argument flags are of long data type, and it can take 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

Put all these constants under a variable and pass it on to clearContents function. So, effectively everything gets cleared. In addition, if you want something specific to be deleted from cells or ranges, you can customize your flags using the above values.

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)

Running the macro

Put below the 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