Solved: Deleting cell contents rather than cell itself

Hello again, I have the following code that deletes a cell once I click the linked button in the worksheet. What I was wondering, was is it possible to edit this so it only deletes the contents rather than the cell itself?

Tanks,

John.

[vba]
Sub deleteMe()
ActiveSheet.Unprotect Password:="password"
ActiveCell.Delete
ActiveSheet.Protect Password:="password", DrawingObjects:=True
End Sub
[/vba]

Try this.
[vba]
Sub deleteMe()
ActiveSheet.Unprotect Password:="password"
ActiveCell.ClearContents
ActiveSheet.Protect Password:="password", DrawingObjects:=True
End Sub [/vba]

Excellent Norie, just what I was looking for.

Thank you very much.

Hi, John! I'm having trouble today understanding what people are asking, so bear with me. What do you mean by "deleting a cell" rather than deleting its contents? :bug:

Hi Sid,

The code, as John had written it, would delete the entire cell, and the rest of the cells in the column would shift up.

Try filling 3 rows by 3 columns with data. =rand() will fill it quite nicely. Then run the following line of code with one of those cells selected.

[vba]Activecell.Delete[/vba]

One of your columns of data will be a little shorter now. ;)

Yes, the original code deleted the cell itself shifting the cells below up one, the 'Activecell.Clearcontents' just clears the cell leaving it blank.

Hope this helps.

John.

Just for reference...

Activecell.Clearcontents will clear the contents of the cell, but leaves the formatting intact. If you want to wipe everything (formats & values) from the cell, you could use:

[vba]Activecell.Clear[/vba]

I find that for most of my applications, that I prefer to leave the formatting of a cell alone once I've set it up. Clear does have it's uses sometimes, though. :)