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

English
, ,
#22

I think the following code will effectively cope with this task.
When analyzing the code, the wonderful book by Andrew Pitonyak OpenOffice.org Macros Explained (OOME_4_1.odt ) can help.

Option Explicit

Sub Test
  Dim oDoc as Object
  Dim oRanges as Object
  Dim oNamedRanges as Object
  Dim dataArray, rowArray, rangeName
  
  oDoc = ThisComponent
  oRanges = oDoc.createInstance("com.sun.star.sheet.SheetCellRanges")
  oNamedRanges=oDoc.NamedRanges
  
  On Error Goto ErrLabel
  ' Loop through cells that contain names of ranges to be cleared
  dataArray = oDoc.getSheets().getByName("Sheet A").getCellRangeByName("A22:B31").getDataArray()
  For each rowArray In dataArray
    For each rangeName In rowArray 
      ' Add the address of the named range to the oRanges object 
      oRanges.addRangeAddress oNamedRanges.getByName(rangeName).ReferredCells.RangeAddress, False
    Next rangeName
  Next rowArray   

  ' In one method call we clear all ranges included in oRanges
  oRanges.clearContents -1
  Exit Sub 
  
ErrLabel:
  Msgbox "Error processing range with name " & rangeName
End Sub
1 Like
#24
  • I still couldn’t see a hint for what reasons named ranges should be used.
    To apply a special cell style to many scattered ranges is much simpler.
  • Named ranges have a scope: Either the complete document or a single sheet. oDoc.NamedeRanges will only contain the first kind.
  • What’s returned by oDoc.NamedRanges or by oneSheet.NamedRanges may actually be a named expression. You need to catch that case to avoid errors.
  • There may be additional named ranges for different purposes, not just for selecting ranges needing to be cleared. If so, you would need to specify special names under a syntactical rule again.

What did I miss?
(Yes, I know that this post is “similar” to the deleted one.")

#25

Dear Wolfgang!


I am still focused on the topic name.
The most efficient clearing method is to collect all the original rectangular cell ranges into one large SheetCellRanges and clear it.
It does not matter whether the ranges to be cleaned are specified by addresses or named ranges.


The macro is of a training nature and is intended specifically for the author’s uploaded file.
The simplest error handling is performed in the macro (you can verify this by running the macro on the uploaded file).

#26

And how do you get the properly filled SheetCellRanges object?
You will need to create an instance, and then to add range addresses taken from somewhere.
If the addresses shall come from named ranges, my objections should be valid. Mainly: too complicated.
If they come from a split string: How to manage adaption to range movement (including insert/delete operations)?

#27

That’s exactly what I do. :slight_smile:

#28

Yes. I saw that. But where do you get the range names or addresses from?
Your example didn’t tell me.
Once more: What did I miss?

#29

@Lupp
The quote above yours looks straight simple, the range_names_to_clear come from Sheet A.A22:B31

btw. IMHO much less obfuscated than your proposal with postfix stylenames with _tail… split and ubound ectpp.

#30

Yes, of course. My comment containing the “What did I miss” was misleading. I was distracted by considerations about where the contents or string values of the used cells should come from and in what way the example A22:B31 could be adapted to changing needs. However, neither a split list nor literal contents from a fixed range in a dedicated sheet allow for a flexible solution.
If you want to get ranges automatically adapting to cell movement (in the wider sense including insertion and partial deletion) based on the mentioned solution, you will need rather complicated formulas (in Sheet A) and if the adaption shall include changes to sheet names you are lost without additional user code.

An example I created and attached above
included the idea to have some cell styles in pairs, each pair consisting of a basical style and of one additionally enabled to obey a clearing command.
I didn’t write about this explicitly because I expexted interested users to understand it without special explanations.
Abandoning this pairing and simply assigning one cell style to all the ranges which should be prepared for clearing on demand would not need the splitting off of a “tail”.

#31

There is also another effective way to programmatically “mark” cells, described in section “15.3. Uninterpreted XML Attributes” of the OOME_4_1.odt Book.
This is an analogue (in my opinion) of your proposal, not directly related to cell formatting.

#32

Thanks for your patience.
Well, I experimented with UserDefinedAttributes years ago, but didn’t get happy. I forgot most about it, and I still don’t know how such attributes assigned to a SheetCellRange object behave concerning cell movement (+insertion/deletion). I also don’t know where to get reliable information on such questions. Even Andrew doesn’t tell much about it.
In addition I’m afraid that rarely used tools may get buggy without notion.
I think I will stick to UI-near tools for tasks of the kind discussed here. Creating and naming cell styles is very simple and reliable. And …

Sub clearOnCommand_Flags7_AllSheets(Optional pModifyers As Long)
For Each sheet In ThisComponent.Sheets
 cfRanges = sheet.CellFormatRanges
 For Each rg In cfRanges
  If rg.CellStyle = "clearOnCommand" Then rg.clearContents(7)
 Next rg
Next sheet
End Sub

… isn’t exactly complicated, and the ranges needing to be treated are easily attributed and changed if needed.

1 Like
#33

Disclaimer:
The idea to use “freestyle-names” to store and deliver composed information isn’t my "intelectual property". The well-known query strings of the internet use structured strings inside of larger constructs acting as “names”.
A procedure I wrote years ago for the manipulation of labels in charts shows me the name of an element as
CID/MultiClick/CID/D=0:CS=0:CT=0:Series=1:DataLabels=:DataLabel=1
very similar to a construct with query string.

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