Deleting objects within a specified cell range

glad_ir

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

Please can somebody help we with this one? Scratching my head....

With macro 1 below I'm trying to delete of any objects in range AA7:AZ60 of my worksheet named COMPARISON then copy either object 22 or 23 (dependant on the value in cell AD5) and paste it into cell AB9 but I'm getting a run-time error "1004 application-defined or object-defined error" and when I debug it flags the "If Not Intersect....." line in the code as the issue.

Any help much appreciated!

thank you,
Iain


VBA Code:
Sub macro1()

    Call Test
    If Range("AD5") = 1 Then macro2
    If Range("AD5") = 2 Then macro4
    
End Sub

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

Sub macro2()

    Call Test
    ActiveSheet.Shapes.Range(Array("Object 22")).Select
    Selection.Copy
    Range("AB9").Select
    ActiveSheet.Paste
    
End Sub

Sub macro4()

    ActiveSheet.Shapes.Range(Array("Object 23")).Select
    Selection.Copy
    Range("AB9").Select
    ActiveSheet.Paste
    
End Sub
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

glad_ir

Board Regular
Joined
Nov 22, 2020
Messages
88
Office Version
  1. 2010
Platform
  1. Windows
OK - update on this one - very weird. The code above works as long as I haven't selected a cell with data validation before running it!

Once I've selected a data validated cell I get the error above when I run the macro.....even if the data validated cell isn't selected when the macro runs and nothing new has been selected from the data validated list. Once the macro errors I can't reset it. If I close the workbook and reopen the macro works fine again.....until I select a data validated cell! The data validated cells are all outside the range in the code.

A weird one for a Saturday night!

Any help appreciated

thanks,
Iain
 
Last edited:

Forum statistics

Threads
1,144,703
Messages
5,725,839
Members
422,646
Latest member
matgabgrp

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