Macro to clear a range of cells

[ShimaYamagoto]

Hey all!

Issue
I would like to create a button to clear cells A2:H2, in Calc. It does not matter much whether they are cleared or set to 0, though I suppose setting to 0 is preferable.
I have a button set up and got this to work in Excel but using LibreBasic is a little staggering for me.

Please let me know if you need any further information. Thank you in advance!

Version
Version: 7.5.0.3 (X86_64) / LibreOffice Community
Build ID: c21113d003cd3efa8c53188764377a8272d9d6de
CPU threads: 12; OS: Windows 10.0 Build 19045; UI render: Skia/Vulkan; VCL: win
Locale: en-US (en_US); UI: en-US
Calc: CL threaded

[Zizi64]

@ShimaYamagoto

I would like to create a button to clear cells A2:H2, in Calc.

Do you want it to do on all of the existing sheets, or on one specific sheet, or on the actually visible sheet, or on the selected sheets?

The recorded macros can work on a static cell range.
You must WRITE your macros (based on the LO API functions), if you want a macro what can work dinamically.

[ShimaYamagoto]

I just need it for one specific sheet. After digging around, I have tried to record a macro.

I select the cells by click+dragging on them. I press Backspace and select to delete their contents. I then saved that macro. However, upon trying to execute it, it will not clear those cells.

For posterity, I will include the macro that it created.

[ShimaYamagoto]

So I was able to get this to work. The issue I was having is the the dialogue window which appears to stop recording a macro was being funky with my inputs.

I had to first select any cell to focus back to my spreadsheet. I then did my operations as needed. I tested both highlighting all given cells that I needed (A2:H2) and deleting them. This worked and I was able to assign a macro to my button for this. Secondly, I tried selected every cell one at a time and pressing 0 to fill that value in. This also worked, though I should add that since this sub is much longer and has to select many cells, it is a tad more resource-intensive for LibreOffice to perform.

I could not find a way to put a value (such as 0) into several selected cells at once. Either way, I have my solution all worked out. Thank you to any readers/posters

[Wanderer]

@ShimaYamagoto

could not find a way to put a value (such as 0) into several selected cells at once.

After you wrote the first 0,
record a cut-and-paste for the 0 marking all cells as destnation - as when you would do this “yourself”. Attention: don’t use copy/paste to avoid the question “do you wish to overwrite occupied cells?”
.
You might also be interested in the solution via the api, as @elmau gave in the fillowing thread:

Macros to write specified cell values to specified cells (& clear them)?

Macros to write specified cell values to specified cells (& clear them) I need numerous ‘write’ and ‘clear’ buttons. I recorded two macros. The ‘clear’ code seems fine … enter the range, the dispatcher then: GoToCell - ClearContents The ‘write’ code can fail because it copies and pastes. If Write is used, and then Clear … it fails because the range is highlighted. Hence I need something like this: args1(0).Name = “Source” args1(0).Value = “$sheet1.$A$3:$D$3” args2(0).Name = “Target” ar…

[ShimaYamagoto]

Hey perfect, I was able to do something like this and it ended up being a little more efficient! Thank you!

[Zizi64]

With API functions:

Sub Fill_A2H2_WithZeros
 
	oSheet = ThisComponent.getcurrentcontroller.activesheet
	for col = 0 to 7
		oCell = oSheet.getcellbyposition(col, 1)
		oCell.value = 0 
	next col
End Sub

FillCellsWithZeros.ods (10.7 KB)