need macro to delete any and all objects in a sheet (SOLVED)

[kskinne]

I need a macro that will check to see if there are any objects in a sheet (pictures, etc.) and if there are, delete them all. Can anyone help me out?

Thank you,
Kevin
This message was edited by kskinne on 2002-10-29 10:01

 

[rikrak]

Something like this?

Sub deleteAllObjects()
ActiveSheet.OLEObjects.Delete
End Sub

 

[kskinne]

I tried that code and it doesn't work in my spreadsheet - it is not deleting the picture in the sheet. Specifically, this is why I need this code:

I have a macro that copies the contents of one sheet as a picture, and then pastes the picture into a second sheet.

Sub copy_picture()
Application.ScreenUpdating = False
Sheets("invoice").Select
Range("A1:L53").CopyPicture
Sheets("copy of invoice").Select
Range("A5").Select
ActiveSheet.Paste
Application.ScreenUpdating = True
End Sub

What I need to do is add a line of code to the macro that will go to the second sheet first, and delete any picture that may have been previously pasted there, before the macro copies and pastes the picture.

 

[kskinne]

I tried that code and it doesn't work in my spreadsheet - it is not deleting the picture in the sheet. Specifically, this is why I need this code:

I have a macro that copies the contents of one sheet as a picture, and then pastes the picture into a second sheet.

Sub copy_picture()
Application.ScreenUpdating = False
Sheets("invoice").Select
Range("A1:L53").CopyPicture
Sheets("copy of invoice").Select
Range("A5").Select
ActiveSheet.Paste
Application.ScreenUpdating = True
End Sub

What I need to do is add a line of code to the macro that will go to the second sheet first, and delete any picture that may have been previously pasted there, before the macro copies and pastes the picture.

 

[Ekim]

You don't need a macro to delete all objects.

1. Press F5.
2. Press the Special button.
3. Click the radio button for Objects.
4. OK.

All objects on your sheet will now be active.
Hit the delete key on your keyboard.

Regards,

Mike

 

[kskinne]

That works fine, plus I discovered I can still automate this by inserting the code for this action as a line in my macro:

ActiveSheet.DrawingObjects.Delete

For some reason OLEObjects.delete didn't work but DrawingObjects.delete does.

Thanks for the help
kevin

 

[Jim North]

Hi you follow Ekim's suggestion with the macro recorder on, you will see Excel's method in VBA.

 

[kskinne]

Yes, that's exactly what I did, so problem solved - thanks to all,

kevin