Monday, June 25, 2012

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.