PDA

View Full Version : Solved: Deleting cell contents rather than cell itself



Johnpants
11-08-2005, 09:19 AM
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
11-08-2005, 09:31 AM
Try this.

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

Johnpants
11-08-2005, 09:35 AM
Excellent Norie, just what I was looking for.

Thank you very much.

Cyberdude
11-08-2005, 12:33 PM
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
11-08-2005, 05:15 PM
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
11-09-2005, 02:39 AM
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
11-09-2005, 07:03 AM
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. :)