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

English
, ,
#1

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.

  1. 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)

  2. 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)

#2

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.

1 Like
#3

clearContents must be faster.

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

#4

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
1 Like
#5

Hallo

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

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 :slight_smile:

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

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

#8

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

#9

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

#10

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

sheet.unprotect( "<your_password>" )
#13

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)

#14

Hi @karolus,
Thanks for your contribution :slight_smile:

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 ?

#11

@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
#12

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)

#15

Hi @sokol92, hi @Lupp,

Thank you very much for your time and expertise !

Unfortunately (for me :wink: ), 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.)

#16

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.

#17
  1. The code I suggested is much simpler than what you tried.
  2. You actually don’t even need to understand that code (though you should be interested).
  3. What you need to understand is the usage of cell styles generally …
  4. … 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)

1 Like
#18

Hi @sokol92,

Thanks again for your time and patience !

I prepared what you asked : a working test file with named ranges and a definition of the task (“Example File With Ranges”)

I also tried to fully understand your code (Sub ClearRange3), but I’m missing a few pieces that I couldn’t find in various LO basic help sources…

What’s the use of the following bits ?

  • oRanges = oDoc.createInstance(“com.sun.star.sheet.SheetCellRanges”)
  • adrRange=oRange.RangeAddress

Why do you use a Step 2 in your For/Next, rather than the by default step 1 ?

What does that line mean ?

  • oRanges.addRangeAddress adrRange, False

Would you be willing to translate ?
If not I totally understand, you already helped a lot !

Have a good day !
Example File With Ranges.ods (26.4 KB)

#19

Hi @Lupp,

Thank you very much for your explanations, they allowed me to perfectly understand what your code does.

However, even after some research (LO Help, Pitonyak’s manual, document foundation wiki) I still don’t get how it works.
I took into account what you said, that I don’t need to understand the code.
But I think I do… For I want to increase my independence and hone my LObasic skills so that I don’t have to rely too heavily on you guys.

So here’s what I don’t quite grasp :

  • What does “cfRgs = oneSheet.CellFormatRanges” do ?
  • How can you use the cfRg variable in “For Each cfRg in cfRgs”, when cfRg is only declared but not initialized ? What meaning does it have ?
  • Is “styleNameParts = Split(cfRg.CellStyle, tailSeparator)” used to separate the beginning of any style name from its ending (after the _) ?
  • In “u = Ubound(styleNameParts)” and “tail = styleNemparts(u)”, how does Ubound give you the end of the style name ? (if I’m not mistaken about what it’s supposed to do)

I’d be very grateful if you could explain, but I’d completely understand that you don’t.

Have a good day !

#20

Thanks. … But that’s not the point. It’s just not a good idea to explain things again (or give a short “tutorial”) per question where topics of much more general relevance are addressed. Too many questions simply in a Q&A site.
However,

  • oneSheet is an object which supports the service com.sun.star.sheet.SheetCellRange. Such objects have a property .CellFormatRanges which provides a collection of SheetCellRange objects of exactly the same cell format. There is no explicit specification about the order or subdivisions of such ranges. See this description . There is also a property UniqueCellFormatRanges which is a bit more complicated, but has advantages. … Use the API documents.
  • For Each ... Next ... is an abbreviating Basic construct for enumerable containers. cfRg gets assigned its “value” per For/Next step.
    For/Next may not have worked as forcefully when Andrew wrote his famous texts. Integer indices may have been needed in such a case to access elements.
  • The styleNameParts are created with the Split() function which returns a 1D-array (sequence) of the parts delimited as described by the second argument with 0-based indices. In my code u is the highest one of these indices and can be used to get the final element of the sequence. The Split() function can split any string.

You see: A very long answer for an extension to an already solved single question.

#21

Understood, I overstayed my welcome in this solved topic. Won’t do it again ! :+1:
Thank you very much for all your answers !

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.