Excel VBA Error 400


Joined
Feb 20, 2018
Messages
1
Reaction score
0
Hi guys,

Currently Im creating a simple macro to delete object (additional excel file as attachment) in a specific range G6:G11:

Here is my code (I got it from the internet, but I edited to suit my needs) :

Sub shpdel()
Dim s As String
Dim shp As Shape
Dim rng As Range

Set ws = ActiveWorkbook.Worksheets("Template")
Set rng = ws.Range("G6:G11")

For Each shp In ActiveSheet.Shapes
With shp
s = .TopLeftCell.Address & ":" & .BottomRightCell.Address
End With
If Not Intersect(rng, ws.Range(s)) Is Nothing Then
shp.Delete
End If
Next

End Sub

The code manage to delete the object that I want to delete but the problem is Im getting error 400 when I execute the code. Already try to follow other suggestion in this forum but no luck i guess. Any idea where the error come from?

Thanks in advance.
 
Ad

Advertisements

Joined
Feb 21, 2018
Messages
216
Reaction score
86
With slight corrections the code is working ....

Sub shpdel()
Dim s As String
Dim shp As Shape
Dim rng As Range

Set ws = ActiveWorkbook.Worksheets("Template")
Set rng = ws.Range("G6:G11")

For Each shp In ActiveSheet.Shapes
With shp
s = .TopLeftCell.Address & ":" & .BottomRightCell.Address
End With
If Not Intersect(rng, ws.Range(s)) Is Nothing Then
shp.Delete
End If
Next

End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top