Ask Your Question

A very simple macro request [closed]

asked 2013-01-20 18:02:11 +0100

anonymous user


updated 2015-10-20 20:41:26 +0100

Alex Kemp gravatar image


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 in Debian if that makes a difference.


edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2015-10-20 20:41:47.503133

1 Answer

Sort by » oldest newest most voted

answered 2013-01-21 09:32:43 +0100

JohnSUN gravatar image

updated 2013-01-22 08:56:59 +0100

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
REM // Once more:
    oCellRangeByName = oActiveSheet.getCellRangeByName("E4:H59")
REM // And so on...
End Sub
edit flag offensive delete link more


@JohnSUN, Try using "REM //" to comment code-snippets. It's hackish, but displays pretty well in the Askbot's markup for code.

qubit gravatar imagequbit ( 2013-01-21 10:20:23 +0100 )edit

@qubit 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?

JohnSUN gravatar imageJohnSUN ( 2013-01-21 11:16:35 +0100 )edit

@JohnSUN, yep, yep -- take a look at the answer that starts "Code commenting styles" on the playground here

qubit gravatar imagequbit ( 2013-01-21 18:19:21 +0100 )edit

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

JohnSUN gravatar imageJohnSUN ( 2013-01-22 08:58:26 +0100 )edit

Question Tools


Asked: 2013-01-20 18:02:11 +0100

Seen: 6,011 times

Last updated: Jan 22 '13