Excel VBA Error 400

J

James Din

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.
 
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
 
Back
Top