An Excel Macro to Delete Pictures

How to delete pictures and textboxes based on a range

I recently created an Excelpicture  macro where I deleted a range of rows from my spreadsheet. However, I had a few pictures and textboxes residing inside the area that I wanted to delete. These objects aren’t tied to any individual cell or row so they were originally not being deleted like I wanted. What to do? Time to edit our Excel vba picture macro.

I added a few lines of code which essentially check if the top left corner of the object intersected with any of the cells contained within the range I wanted to delete. If so, and the object was a picture, then it was deleted. Same for the textboxes.

‘Delete pictures

Dim Sh As Shape

   For Each Sh In ActiveWorkbook.Worksheets(I).Shapes

       If Not Application.Intersect(Sh.TopLeftCell, Range("A40:AA120")) Is Nothing Then

         If Sh.Type = msoPicture Then Sh.Delete

             End If

    Next Sh

'delete text boxes

 For Each Sh In ActiveWorkbook.Worksheets(I).Shapes

       If Not Application.Intersect(Sh.TopLeftCell, Range("A40:AA120")) Is Nothing Then

                  If Sh.Type = msoTextBox Then Sh.Delete

               End If

    Next Sh

By using macros in Excel, this process was repeated inside code which looped through all the sheets in my open workbook. To loop through all worksheets:

Dim WS_CountAs Integer

 Dim I As Integer

' SetWS_Count equal to the number of worksheets in the active workbook.

WS_Count = ActiveWorkbook.Worksheets.Count

' Begin the loop through all worksheets.

   For I = 1 ToWS_Count

'active sheet i

ActiveWorkbook.Worksheets(I).Activate

‘insert delete pictures and textboxes here

Next I

As with most computer programs, there are multiple ways to go about the same task and it’s up to you to pick the best one. Another option is this:

Dim shpAs Shape

    For Each shpInActiveSheet.Shapes

        'Debug.Printshp.Name, shp.TopLeftCell.Row, shp.BottomRightCell.Row

        If shp.TopLeftCell.Row>= 5 Andshp.BottomRightCell.Row<= 39 Then

shp.Delete

        End If

    Next

Take Advanced Excel Training to learn how to write VBA macros in Excel.