Solved: Deleting cell contents rather than cell itself

[Johnpants]

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.


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

[Norie]

Try this.

Sub deleteMe()
ActiveSheet.Unprotect Password:="password"
ActiveCell.ClearContents
ActiveSheet.Protect Password:="password", DrawingObjects:=True
End Sub

[Johnpants]

Excellent Norie, just what I was looking for.

Thank you very much.

[Cyberdude]

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:

[Ken Puls]

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.

Activecell.Delete

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

[Johnpants]

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.

[Ken Puls]

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:

Activecell.Clear

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. :)