A very simple macro request

[Creqz]

Hello,

I’m trying to create a macro that deletes the contents of cells A4:C59 as well as cells E4:H59, and by “delete” I’m referring to the action performed when a cell is selected and the ‘Delete’ key is pressed on the keyboard; I’d like to keep style/formatting untouched and I’d like this macro to be accessible from any spreadsheet.

I know I’m being lazy, but 99.9% of the time I use Calc I’m simply copying sheets and then deleting the cells previously mentioned. If I used Calc more often and/or used Calc to do other things I’d happily read through all the tutorials and guides and learn to do this myself. The fact is I don’t though and I just spent most of this morning trying to find how to do this to no avail.

I also tried using the ‘Record Macro’ tool but I must’ve done something wrong because the cells deleted while recording the macro != the cells deleted when running the macro.

I’m using LO 3.5.4.2. in Debian if that makes a difference.

Thanks

[JohnSUN]

It’s really very simple:

Sub clrRngs
Dim oActiveSheet As Variant
Dim oCellRangeByName As Variant
REM // Get current sheet:
	oActiveSheet = ThisComponent.getCurrentController().getActiveSheet()
REM // Get one range to clear...
	oCellRangeByName = oActiveSheet.getCellRangeByName("A4:C59")
REM // ... and clear:
	oCellRangeByName.clearContents(7) REM // ' Clear only VALUE(1)+DATETIME(2)+STRING(4)
REM // Other CellFlags see  at http://www.openoffice.org/api/docs/common/ref/com/sun/star/sheet/CellFlags.html
REM // Once more:
	oCellRangeByName = oActiveSheet.getCellRangeByName("E4:H59")
	oCellRangeByName.clearContents(7)
REM // And so on...
End Sub

[qubit1]

@johnsun, Try using “REM //” to comment code-snippets. It’s hackish, but displays pretty well in the Askbot’s markup for code.

[JohnSUN]

@qubit1 Sorry, my friend! After a few experiments with “REM / /” couldn’t get a good result. Can you show a small example or a link to it?

[qubit1]

@JohnSUN, yep, yep – take a look at the answer that starts “Code commenting styles” on the playground here

[JohnSUN]

Yes, you’re right! So much better. I do not see the difference, because trying to see it in the preview

[lonk]

For protected sheet, unprotect it first.

oActiveSheet.UnProtect("")

And re-protect after finishing contents clearing.

oActiveSheet.Protect("")