Deleting objects in a range

glad_ir

Board Regular
Joined
Nov 22, 2020
Messages
88
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

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,691
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
 
Solution

glad_ir

Board Regular
Joined
Nov 22, 2020
Messages
88
Office Version
  1. 2010
Platform
  1. Windows
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
 

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
1,811
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
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
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,691
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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?
 

glad_ir

Board Regular
Joined
Nov 22, 2020
Messages
88
Office Version
  1. 2010
Platform
  1. Windows
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
 

glad_ir

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

Thank you for the alternative code. Much appreciated.

Iain
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,691
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,147,518
Messages
5,741,636
Members
423,674
Latest member
Charles2dodo

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
Top