Excel macro to clear cell contents, textboxes, pictures, and lines within a range

Clearing the cell contents using an Excel macro is very easy (and if you’re unfamiliar see my getting started with macros quick start guide). In fact, it can be done with only one line of code. To clear the contents of range B4 to C30 for example:

ActiveSheet.Range("B4:C30").ClearContents

But this macro will only delete the contents (numbers and words) inside the cells of the active sheet. What if you also want to delete any shapes within the range, including textboxes, pictures, and lines? You can use OLEFormat.Object to identify specific types of shapes and delete the ones you want. This is a better method than simply deleting every single shape within a sheet.

So the code below basically:

1. Looks for all shapes in the active sheet
2. Picks out the type specified, for example it will look for all the shapes that are of type TextBox
3. Checks if the top left corner of the specified shape falls within the defined range of cells
4. If the selected shape is within the range, then delete that shape

Sub DeleteCellContentsInARange()

'clear cell contents in the specified range

ActiveSheet.Range("B4:C30").ClearContents

'Delete TextBoxes

Dim Tbox As Shape

For Each Tbox In ActiveSheet.Shapes

If TypeName(Tbox.OLEFormat.Object) = "TextBox" Then

  If Not Intersect(Range(Tbox.TopLeftCell.Address), Range("B4:C30")) Is Nothing Then

    Tbox.Delete

End If

End If

Next Tbox

'Delete lines and arrows in Excel

Dim Tline As Shape

For Each Tline In ActiveSheet.Shapes

If TypeName(Tline.OLEFormat.Object) = "Line" Then

  If Not Intersect(Range(Tline.TopLeftCell.Address), Range("B4:C30")) Is Nothing Then

    Tline.Delete

End If

End If

Next Tline

'Delete Pictures

Dim TPic As Shape

For Each TPic In ActiveSheet.Shapes

If TypeName(TPic.OLEFormat.Object) = "Picture" Then

  If Not Intersect(Range(TPic.TopLeftCell.Address), Range("B4:C30")) Is Nothing Then

    TPic.Delete

End If

End If

Next TPic

End Sub

You could easily add more to the code and have it loop through every sheet in a workbook instead of just the active sheet. I hope that helps!