VBA: Macro to delete range runs normally, gives error 1004 if I click on a cell with data validation

[RichardHell]

This is my first post here, so sorry if I made any mistakes (this forum has helped me a lot before tho, thanks!).

I'm trying to make a macro that will delete everything within a range of cells, but I'm running into a weird issue.

There's a couple of checkboxes and a picture that don't get deleted with the .Delete method, so I include a loop that goes through every shape and deletes it if it's inside the range. This all works perfectly fine, until I click on one of the cells that has data validation. Here's the code:

Dim sh As shape
Dim ws As Worksheet
Dim rangeval As range

Set ws = Worksheets("Historial")
Set rangeval = ws.range("A1:K53")

For Each sh In ws.shapes
    If Not Intersect(sh.TopLeftCell, rangeval) Is Nothing Then
        sh.Delete
    End If
Next

rangeval.Delete (xlShiftUp)

Throughout the range I've got some cells that pull data from a list located in a different sheet, using data validation, like this:

The problem is when I click on any of these cells. I don't even have to edit the content, or do anything. As soon as I click on one of them, even if I click away, it gives me an error.

When I Debug them problem seems to be in this line:

If I check the range that I'm trying to delete after running the macro it does seem to delete every shape, but gets stuck in (maybe?) the final iteration of the loop. Is it trying to pull something from the sheet that has the List values? I'm out of ideas honestly, any help will be appreciated.

 

[Fluff]

Hi & welcome to MrExcel.
A brute force approach

Dim sh As Shape
Dim ws As Worksheet
Dim rangeval As Range, Tlc As Range

Set ws = Worksheets("Historial")
Set rangeval = ws.Range("A1:K53")

For Each sh In ws.Shapes
   Set Tlc = Nothing
   On Error Resume Next
   Set Tlc = sh.TopLeftCell
   On Error GoTo 0
   If Not Tlc Is Nothing Then
      If Not Intersect(sh.TopLeftCell, rangeval) Is Nothing Then
          sh.Delete
      End If
   End If
Next

rangeval.Delete (xlShiftUp)

 

[RichardHell]

Thank you so much, this worked! I'm kind of a VBA noob, so if you don't mind, could you mind telling me what it does? Is it just telling excel to ignore any errors that come up?

 

[Fluff]

Is it just telling excel to ignore any errors that come up?

Pretty much. It tries to set the range variable Tlc to the Top Left Corner of the shape & if it can't then Tlc will be nothing so it checks for that