I have a routine which deletes all the contents of a
sheet. Clear All only takes out the text, and
formatting. How do I also get rid of any charts, buttons
or other objects? Without deleting the sheet, I need to
make the sheet completely empty.


THanks

Hi
Dim Obj
For Each Obj In ActiveSheet.Shapes
Obj.Delete
Next Obj

HTH
Cordially
Pascal

"ExcelMonkey" a écrit dans le message
de ...
I have a routine which deletes all the contents of a
sheet. Clear All only takes out the text, and
formatting. How do I also get rid of any charts, buttons
or other objects? Without deleting the sheet, I need to
make the sheet completely empty.


THanks

Be careful with this, it also deletes the data validation and autofilter
dropdown arrows if those facilities are currently in use. Worst thing is
that no-one seems to know how to get them back for that sheet.

See this discussion between Dave Peterson and myself on the problem and a
solution http://tinyurl.com/3lgq5.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"papou" wrote in message
...
Hi
Dim Obj
For Each Obj In ActiveSheet.Shapes
Obj.Delete
Next Obj

HTH
Cordially
Pascal

"ExcelMonkey" a écrit dans le
message
de ...
I have a routine which deletes all the contents of a
sheet. Clear All only takes out the text, and
formatting. How do I also get rid of any charts, buttons
or other objects? Without deleting the sheet, I need to
make the sheet completely empty.


THanks