Deleting All Types of Contents from Calc Range using Macro

Advertisement

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")

Range A3:B10 with different types of values

Range A3:B10 with different types of values

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

You may also like...

  • Jim Smith

    Thanks for the push in the right direction. I took your info along with some helpful hints in some forums and came up with this snippet of code to use. I do the programming for my wife’s gradebook for her classes. It has always been a time consuming task to copy the worksheet to a new grading period and then clear all the existing grades. The sheets are all contiguous and the ranges are the same for every sheet, so this is what I did!

    ****** code follows I run this with a HUGE RED button that warns that there is no built in undo. BACKUP first.

    sub delete_grades ‘ subroutine to delete all grades on the active sheet

    Dim oDoc As Object
    Dim oSheet As Object ‘ I want SFPW through HabitsNumeric aka sheets 4-10
    Dim oRange As Object
    Dim i As Integer

    oDoc = ThisComponent
    For i = 4 to 10 ‘ sheet 4 which is human 5 since the number starts at 0 for sheet number’
    oSheet = oDoc.Sheets(i) ‘ this loop iterates through all seven sheets and clears the range.
    oRange = oSheet.getCellRangeByName(“D1:BU26”)
    oRange.clearContents(7) ‘ the ranges never have anything but strings, datetime, or numbers, so the
    next i ‘ 7 flag handles all of those and leaves all my formatting and styles intact.
    MsgBox(“Finished”) ‘ this completes in mere seconds even when full of grades.
    Done:
    ::
    end sub ‘delete_grades

    Hope someone can use it.

    • @disqus_xo2FJypWGT:disqus – Thanks for your input. I am glad my article helped you to solve problem. Flag 7 was not in my list, I will add as note. Do follow Debugpoint.com for more tutorials.

Translate »