Deleting objects in a range

glad_ir

Board Regular
Joined
Nov 22, 2020
Messages
143
Office Version
  1. 2010
Platform
  1. Windows
Hi,

Hoping somebody can help me with this one please. I need some code that will delete all objects within a range....in particular AA1:AZ50 on a worksheet named "COMPARISON". I found the code below on the forum but it doesn't delete the objects for me.

VBA Code:
Sub Test()
Dim Sh As Shape
With Worksheets("COMPARISON")
   For Each Sh In .Shapes
       If Not Application.Intersect(Sh.TopLeftCell, .Range("AA1:AZ50")) Is Nothing Then
         If Sh.Type = msoShape Then Sh.Delete
       End If
    Next Sh
End With
End Sub

The object names will vary so I can't reference them specifically.

Is anybody able to help me please?

Thanks, Iain
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Try removing this line from the code you posted:
If Sh.Type = msoShape Then Sh.Delete

EDIT: Sorry that should be try replacing:
If Sh.Type = msoShape Then Sh.Delete
with
Sh.Delete
 
Upvote 0
Solution
Hi Joe,

Thank you for replying. I get an error when I make this change. Have I understood correctly with the code below?

VBA Code:
Sub Test()
Dim Sh As Shape
With Worksheets("COMPARISON")
   For Each Sh In .Shapes
       If Not Application.Intersect(Sh.TopLeftCell, .Range("AA1:AZ50")) Is Nothing Then
         Sh.Delete
       End If
    Next Sh
End With
End Sub

Thanks,
Iain
 
Upvote 0
Maybe try:

VBA Code:
Sub Test()
'
    Dim sh As Shape
'
    For Each sh In Worksheets(Comparison).Shapes
        If Not Intersect(sh.TopLeftCell, Range("AA1:AZ50")) Is Nothing Then
            sh.Delete
        End If
    Next sh
End Sub
 
Upvote 0
Hi Joe,

Thank you for replying. I get an error when I make this change. Have I understood correctly with the code below?

VBA Code:
Sub Test()
Dim Sh As Shape
With Worksheets("COMPARISON")
   For Each Sh In .Shapes
       If Not Application.Intersect(Sh.TopLeftCell, .Range("AA1:AZ50")) Is Nothing Then
         Sh.Delete
       End If
    Next Sh
End With
End Sub

Thanks,
Iain
What error message do you get and which line is highlighted when you get it?
 
Upvote 0
Hi Joe,

That's weird - I restarted my laptop and it is working fine. No idea what was going on there!

Thank you for your help with this one. Much appreciated!

cheers,
Iain
 
Upvote 0
Hi Johnny,

Thank you for the alternative code. Much appreciated.

Iain
 
Upvote 0
Hi Joe,

That's weird - I restarted my laptop and it is working fine. No idea what was going on there!

Thank you for your help with this one. Much appreciated!

cheers,
Iain
You are welcome - thanks for the reply.
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,927
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top