CODEWITHSUNDEEP
vbaexcel
Selrac
Selrac

Reputation: 2293

Excel shapes errors

Following the code I found here, I'm trying to count the number of shapes in a worksheet, but I'm getting an 'overflow' error. The code I'm using is as follows:

Sub tst()
    Dim i As Integer
    i = Sheets("DB_AL").Shapes.count
    MsgBox i
End Sub

The problem, I think is the number of shapes that there are in the worksheet.

I'm not sure how I can delete them either. I tried also deleting the shapes with the code I found here:

Sub delShapes()
    Dim shape As Excel.shape
    For Each shape In Sheets("DB_AL").Shapes
        shape.Delete
    Next
End Sub

But I'm getting also an 'out of range' error.

At this stage, I'm more interested on deleting the shapes as it is making the worksheet un-usable.

Any ideas?

Upvotes: 0

Views: 1430

Answers (4)

Teasel
Teasel

Reputation: 1340

Overflow error

In your code you're trying to get the result of Shapes.Count into an Integer, the problem you're facing is because Shapes.Count return a Long.

The following code should work as you expect:

Sub tst()
    Dim i As Long
    i = Sheets("DB_AL").Shapes.count
    MsgBox i
End Sub

Deleting all shapes in your sheet

When you're using a For Each loop you do not need to declare the object, you can simply go through all shapes with the following code:

Sub delShapes()
    For Each shapeToDelete In ThisWorkbook.Sheets("DB_AL").Shapes
        shapeToDelete.Delete
    Next shapeToDelete
End Sub

Upvotes: 2

LatifaShi
LatifaShi

Reputation: 440

Try The code below :

Sub delShapes()
    Dim i As Long
    Dim x As Long
    i = Sheets("DB_AL").Shapes.Count
    For x = i To 1 Step -1
         Sheets("DB_AL").Shapes(x).Delete
    Next x
End Sub

Upvotes: 1

arun v
arun v

Reputation: 850

Over flow issue is explained by Teasel,

to delete the shapes you can use this code

Sub delShapes()
Dim shape As shape, ws As Worksheet
Set ws = ThisWorkbook.Sheets("DB_AL")
For Each shape In ws.Shapes
    shape.Delete
Next
End Sub

Upvotes: 1

Foxfire And Burns And Burns
Foxfire And Burns And Burns

Reputation: 11978

The Overflow error is because the data you are woerking with is too big for your variable. You have defined i As Integer, but Sheets("DB_AL").Shapes.count returns a Long type, and this raises an error. Just define i As Long and it will work.

More info about Data Types

To delete all the shapes, try:

ActiveSheet.DrawingObjects.Delete

Upvotes: 0

Related Questions