CODEWITHSUNDEEP
vbaexcel
Tim Wilkinson
Tim Wilkinson

Reputation: 3791

VBA delete all shapes error

I noticed my worksheet was slowing right down, and found a few objects had been created for some reason. I ran the following macro to remove them all:

Dim Shp as Shape
For Each Shp In Worksheets("Sheet1").Shapes
    Shp.Delete
Next Shp

However I keep getting The index into the specified collection is out of bounds error. So I did the following to see how many objects had been created:

Dim i As Long
i = ActiveSheet.Shapes.Count
MsgBox CLng(i)

And received the number 41152. I have tried Go To Special and selected objects, but if this number is correct my computer is crashing trying to select over 41K objects at once.

Any suggestions how to remove them all?

Upvotes: 0

Views: 1498

Answers (1)

Scott Holtzman
Scott Holtzman

Reputation: 27269

You can step backward through the Shapes collection to delete them from the last value in the collection Index to the first.

Like this:

i = Worksheets("Sheet1").Shapes.Count
For x = i To 1 Step -1: Worksheets("Sheet1").Shapes(x).Delete: Next

Upvotes: 1

Related Questions