What would be the most efficient way to clear a great deal of cells at once?

Elvanthyell · July 23, 2025, 1:39pm

Hi there !

My version information :
Version: 25.2.4.3 (X86_64) / LibreOffice Community
Build ID: 33e196637044ead23f5c3226cde09b47731f7e27
CPU threads: 8; OS: Windows 10 X86_64 (10.0 build 19045); UI render: Skia/Vulkan; VCL: win
Locale: fr-FR (fr_FR); UI: en-US
Calc: CL threaded

The context :
I need to clear a a few hundreds of cells at once, across a dozen sheets, by clicking a button.
I thus wrote a macro and associated it to the “button pressed” event.
I tested two ways of programming the macro and roughly timed both of them.
I think it doesn’t matter to me if the cells are filled with 0 or “”, or if they are just cleared out.
Below are examples of the two ways I used.

Relying upon the clearContents method
ThisComponent.Sheets.getbyname(SheetName.string).getCellRangeByName(“Cell1:Cell3”).clearcontents(1)
ThisComponent.Sheets.getbyname(SheetName.string).getCellRangeByName(“Cell4:Cell6”).clearcontents(4)
Relying upon the setvalue/setstring methods
ThisComponent.Sheets.getbyname(SheetName.string).getCellRangeByName(“Cell1”).setvalue(0)
ThisComponent.Sheets.getbyname(SheetName.string).getCellRangeByName(“Cell2”).setvalue(0)
ThisComponent.Sheets.getbyname(SheetName.string).getCellRangeByName(“Cell3”).setvalue(0)
ThisComponent.Sheets.getbyname(SheetName.string).getCellRangeByName(“Cell4”).setstring("")
ThisComponent.Sheets.getbyname(SheetName.string).getCellRangeByName(“Cell5”).setstring("")
ThisComponent.Sheets.getbyname(SheetName.string).getCellRangeByName(“Cell6”).setstring("")

With my original file, the clearcontents method takes way more time than the setvalue/setstring method.
Futhermore, the more cells are filled at the beginning, the more the difference between those to methods seems significant.

So my question is : What would be the most efficient way to clear a great number of cells at once ?
(Be it in terms of cpu usage and/or timewise.)

Edit (2025-07-24T09:19:00Z):
Ok, here are the latest developments.
I think it’s still relevant to talk about that here, but I’m sorry if I’m wrong !

A) An inconclusive testing
I spent a good deal of time setting up a file to test the clearing speed of various methods…

I filled several cells and cell ranges with letters, among 18 sheets.
There was a total of approximately 4000 cells to clear.
I used 3 methods :
clearContents(7)
clearContents(4)
setstring("")

… Only to find out that the clearing was instantaneous, regardless of the method used.

B) The obvious question
Why would it take several seconds (6 to 20) to clear out less than 1000 cells in my original file, when 4000 cells are instantaneously cleard out in my test file ?

Unfortunately, I can’t share my orignal file, it’s kinda private. However, I made a template of how cells are distributed in it.
Moreover, the differences between original and test files are listed below.
Whereas the test file is only filled with letters, the original file contains :

formulas, including references between sheets,
drop down lists (based on Data Validation),
2 check boxes,
conditional and direct formatting (which shouldn’t have any effect since I’m only using clearContents(4) and (1), not taking the format into account ?)

C) The available elements
The template mimicking my original file : “Original File Template”

The pieces of code I’m using to clear out the original file, and comparing the clearing speed between clearContents(1)/(4) and setvalue/setstring : “Clearing Cells - clearContents” and “Clearing Cells - setvalue+setstring”
(All variables are declared at the beginning of the macro, and initialised in another Sub.)

For my original file, the setvalue/setstring method is still way faster than the clearContents method.

I hope it is enough for you connoisseurs to go on…

Original File Template.ods (44.3 KB)
Clearing Cells - clearContents.odt (36.1 KB)
Clearing Cells - setvalue+setstring.odt (38.4 KB)

sokol92 · July 23, 2025, 1:49pm

Such volumes must be processed quickly.

This is an interesting question.
It would be great if you uploaded a test file and already written macros for clearing cells, so that there would be something to compare with.

mikekaganski · July 23, 2025, 1:54pm

clearContents must be faster.

If it isn’t, it would be nice to file a bug report with a sample.

schiavinatto · July 23, 2025, 1:55pm

Se puder deixar as células a limpar, desprotegidas e proteger a planilha, seria selecionar toda a planilha e usar Delete. ( Não funciona )

If you can leave the cells to be cleared, unprotected and protect the worksheet, it would be to select the entire worksheet and use Delete. ( Not working )

Use the macro

Sub ClearUnprotectedCellsFromArea()
    Dim oDoc As Object, oRange As Object
    Dim oCell As Object
    Dim iRow As Long, iCol As Long

    oDoc = ThisComponent
    oRange = oDoc.NamedRanges.getByName("Area").getReferredCells()

    For iRow = 0 To oRange.Rows.Count - 1
        For iCol = 0 To oRange.Columns.Count - 1
            oCell = oRange.getCellByPosition(iCol, iRow)

            ' Attempts to clear the cell — if it is protected, it generates an error, which will be ignored
            On Error GoTo Skip
            oCell.clearContents(7) ' Limpa valor, texto e fórmula
Skip:
            On Error GoTo 0 ' Clears value, text, and formula
        Next iCol
    Next iRow
End Sub

karolus · July 23, 2025, 2:00pm

Hallo

thisComponent.Sheets(0).clearContents( -1 )

Elvanthyell · July 23, 2025, 2:08pm

Well, as for me, it can take up to 70-90 sec to clear roughly 800-1200 cells.
And it seems that the more the targeted cells are filled beforehand, the more time it takes.

I could try that indeed !

Alright, I’m gonna test it out more cleanly.
If my previous results are confirmed, I will file a bug report

Thank you very much, I’m gonna try that asap !

I’m sorry, what is it supposed to do exactly ?

karolus · July 23, 2025, 3:07pm

Clears any content of the whole first sheet in one strike!

Elvanthyell · July 23, 2025, 3:09pm

Oh ok ! Do you know if it clears protected cells too ?

karolus · July 23, 2025, 3:16pm

No, you should unprotect the sheet with the cells before…

sheet.unprotect( "<your_password>" )

Lupp · July 24, 2025, 2:05pm

If you have a look into my example with macro, you will find the point where I suppose a related bug:
It seems the property/method CellFormatRanges under some conditions wrongly returns a range with empty CellStyle name.
See also:
CellFormatRanges_Bug_extremely_reduced.ods (10.9 KB)

Elvanthyell · July 25, 2025, 8:38am

Hi @karolus,
Thanks for your contribution

I couldn’t get your suggestion to work, with the following code :

ThisComponent.Sheets(0).protect("")
ThisComponent.Sheets(0).clearContents( -1 )
ThisComponent.Sheets(0).unprotect("")

Somehow, the unprotected cells are not cleard out…
If I’m not mistaken, the fact that the sheet is protected shouldn’t prevent the unprotected cells to be cleared out ?
Did I miss something ?

sokol92 · July 24, 2025, 11:35am

@Elvanthyell , thank you very much for the prepared example!
In my opinion, it is not very suitable for comparing the efficiency of cell clearing methods, as it is overloaded with details.
I propose a simpler and more visual test bench for testing the efficiency of clearing methods.
Task: clear the cells of all rows of the range with even row numbers (every other row).
Starting macro - Test. You can change the values of the variables nRows and nCols, which specify the number of rows and columns in the range.
On my configuration, the macro produces the following message at the end of its work (the running time of the clearing macros in milliseconds):

Rows: 1000
Columns: 10

ClearRange1: 7188
ClearRange2: 879
ClearRange3: 60

Update.
All macros (ClearRange1, ClearRange2, ClearRange3) use the clearContents method.
The ClearRange1 method clears cells one by one, the ClearRange2 method clears row by row, and the ClearRangе3 method clears all required cells at once.

Notice the huge difference in performance.

Option Explicit

Function FillRange(Byval nCols, Byval nRows) as Object
  Dim oDoc as Object, oRange as Object, i as Long
  oDoc = StarDesktop.LoadComponentFromUrl("private:factory/scalc", "_default", 0, Array())
  oRange=oDoc.Sheets(0).getCellRangeByPosition(0, 0, nCols - 1, nRows - 1)
  For i=0 To nCols - 1
    oRange.getCellByPosition(i, 0).setValue i
  Next i
  oRange.fillSeries 0, 0, 0, 0, 0
  FillRange = oRange
End Function


Sub ClearRange1(Byval oRange as Object)
  Dim i as Long, j as Long, nRows as Long, nCols as Long
  
  nRows = oRange.Rows.Count
  nCols = oRange.Columns.Count
  
  For i=1 To nRows - 1 Step 2
    For j = 0 To nCols - 1
       oRange.getCellByPosition(j, i).clearContents -1
    Next j
  Next i     
End Sub


Sub ClearRange2(Byval oRange as Object)
  Dim i as Long, nRows as Long, nCols as Long
  
  nRows = oRange.Rows.Count
  nCols = oRange.Columns.Count
  
  For i=1 To nRows - 1 Step 2
    oRange.getCellRangeByPosition(0, i, nCols-1, i).clearContents -1
  Next i     
End Sub


Sub ClearRange3(Byval oRange as Object)
  Dim i as Long, nRows as Long, nCols as Long
  Dim oDoc as Object, oRanges as Object, adrRange
  oDoc = oRange.Spreadsheet.DrawPage.Forms.Parent
  oRanges = oDoc.createInstance("com.sun.star.sheet.SheetCellRanges")
  
  nRows = oRange.Rows.Count
  nCols = oRange.Columns.Count
  adrRange=oRange.RangeAddress
  
  For i = 1 To nRows - 1 Step 2
    adrRange.StartRow = i
    adrRange.EndRow = i 
    oRanges.addRangeAddress adrRange, False
  Next i     
  oRanges.clearContents -1
End Sub


Sub Test()
  Dim oRange as Object
  Dim nRows as Long, nCols as Long, t as Long, s as String, n as Long
  
  nRows = 1000  ' ???
  nCols =  10   ' ???
  
  For n=1 To 3
    oRange = FillRange(nCols, nRows)
    t = getSystemTicks()
    
    If n = 1 Then
      ClearRange1 oRange
    ElseIf n = 2 Then   
      ClearRange2 oRange
    Else
      ClearRange3 oRange  
    End If
    
    s = s & Chr(10) & "ClearRange" & n & ": " & (getSystemTicks() - t)
  Next n  
  
  MsgBox "Rows: " & nRows & Chr(10) & "Columns: " & nCols & Chr(10) & s
End Sub

Lupp · July 24, 2025, 1:48pm

The author of the OQ seems to intend to define the ranges needing to be cleared by assigning each such range to a named expression (aka named range).
I didn’t find a comment that dissents from this concept, and provide an example showing how to do the task very efficiently by using cell styles which tell by their names that the contents should be cleared on command.
Raw sheets:
disask124702Raw.ods (25.8 KB)
Sheets document with macro.
disask124702Play.ods (28.1 KB)

Elvanthyell · July 25, 2025, 8:41am

Hi @sokol92, hi @Lupp,

Thank you very much for your time and expertise !

Unfortunately (for me ), my lack of LO Basic skills leaves me unable to benefit from your suggestions. (to many unknown bits of code)

How could I implement your findings to effectively clear out lonely cells AND cell ranges amongst several sheets ?
Would I be so bold as to ask you to give me a functional example of the above ?
(Something a bit more like @schiavinatto’s proposition for instance.)

sokol92 · July 25, 2025, 10:43am

Hello @Elvanthyell!
About the title of this topic. My example contains 3 macros, the results of which are the same (clearing 500 ranges containing a total of 5000 cells), but the speed differs by 2 orders of magnitude.
Please prepare your working test file (in your starting example, the document does not contain the required named ranges) and the task - what needs to be cleared.

Lupp · July 25, 2025, 11:08am

The code I suggested is much simpler than what you tried.
You actually don’t even need to understand that code (though you should be interested).
What you need to understand is the usage of cell styles generally …
… and of giving specialised names to them making them capable of cotrolling the effects of macros.

See new attachment.
disask124702_ClearTemporaryContents.ods (27.0 KB)