Another debug error # 1004

  • Thread starter Thread starter Dave Peterson
  • Start date Start date
D

Dave Peterson

There are a lot of shapes that you can't cut--comments, data|validation
dropdowns, dropdown arrows from autofilter.

If you know what you want to cut, you may be able to limit your loop to just
those kinds of shapes (say commandbuttons or buttons or dropdowns or
comboboxes).

Ron de Bruin has some very nice notes here:
http://www.rondebruin.nl/controlsobjectsworksheet.htm
 
Can someone help me with this?
I can not figure out why it is happening.
I get a debug error # 1004 on the line that says sh.cut.
It works fine all the formulas are gone but it will not continue with the
rest of the code.
The sheet has a lot of data in it.

CODE

With ActiveSheet
..Name = myname

'removes formulas
..UsedRange.Value = .UsedRange.Value

For Each sh In .Shapes

'Debug is on next line

sh.Cut
Next sh
End With
 
Hi,

Why not record the cut command for one of your shapes and post the code for
us stating that you want to do .... for all these items in the collection.
 
Sub DeleteSheetDrawingObjects()
'deletes shapes leaves comments and drop downs
ActiveSheet.DrawingObjects.Delete
'==
For Each ole In activesheet.OLEObjects
ole.Delete
Next ole
end Sub
 
Hi,

If you are trying to delete the shapes by cutting them its simplier to just
Delete them. If comments are closed then

Sub DeleteShapes()
ActiveSheet.DrawingObjects.Delete
End Sub

deletes all shapes on a sheet ignoring comments, if comments are exposed
then they will be deleted also.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


Microsoft Communities said:
What I want to do is remove all formulas but keep the data. There is a
button on the sheet and I want to keep it. The code I am using was given
here from this news group.
Click to expand...
 
I'm not sure what you mean.

If you want to remove the formulas, but keep the results of the formulas, then
record a macro when you select your range (all the cells???) and do an Edit|Copy
followed by Edit|Paste special values.

If you want to clear the contents of all the cells with formulas, then select
the range and hit F5 (edit|Goto). Then click special, then Formulas, then ok.
Then finally hit the delete key on the keyboard.
 
What I want to do is remove all formulas but keep the data. There is a
button on the sheet and I want to keep it. The code I am using was given
here from this news group.
 
I just want to delete the formulas and leave the data.
I tried to do a copy paste special values but get the error about cells must
match I do not care if the cells match or not I just want the data in the
same place.

Shane Devenshire said:
Hi,

If you are trying to delete the shapes by cutting them its simplier to
just
Delete them. If comments are closed then

Sub DeleteShapes()
ActiveSheet.DrawingObjects.Delete
End Sub

deletes all shapes on a sheet ignoring comments, if comments are exposed
then they will be deleted also.
Click to expand...
 
The edit paste values would not work because of cells joined.

But I did get everything to work properly.
Thank you everyone for all your help.
 
Merged cells are a pain to work with.

Maybe you could copy|paste special in smaller areas--or remove the merged cells.

Microsoft said:
The edit paste values would not work because of cells joined.

But I did get everything to work properly.
Thank you everyone for all your help.
Click to expand...
 
Back
Top