Monday, November 13, 2017

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!