Excel VBA: SmartDel Macro

Sometimes working with Excel need to clear cells with numeric data only and leave cells with formulas and text. Today we'll write macro SmartDel, which clear cells with numers in the selected range.

Sub SmartDel()
    Application.ScreenUpdating = False
 
    Dim selRange As Range
    Set selRange = Intersect(Selection, ActiveSheet.UsedRange)
 
    If selRange Is Nothing Then
        GoTo exit_SmartDel
    End If
 
    For Each myRange In selRange
        If IsNumeric(myRange.Formula) = True Then
            If ActiveSheet.ProtectContents = False Then
                myRange.MergeArea.ClearContents
            Else
                If myRange.Locked = False Then
                    myRange.MergeArea.ClearContents
                End If
            End If
        End If
    Next myRange
 
exit_SmartDel:
    Application.ScreenUpdating = True
End Sub

Let's look inside the code:

1. Turn screen updating off to speed up our macro

   Application.ScreenUpdating = False

2. Declare variable as an Range

   Dim selRange As Range

3. Set intersection of two ranges: the current selection range and the used range of active sheet. This intersection allows us to ignore an unused cells and speed up the macro.

   Set selRange = Intersect(Selection, ActiveSheet.UsedRange)

4. If intersection of two ranges are empty then stop the macro

   If selRange Is Nothing Then
       GoTo exit_SmartDel
   End If

5. Loop for each cell in the range

   For Each myRange In selRange

6. If cell is recognized as a number then we clear the formula from tha range, but leave the formatting

   If IsNumeric(myRange.Formula) = True Then
       myRange.MergeArea.ClearContents
   End If

7. Method MergeArea allows us to work with merged cells too

   myRange.MergeArea.ClearContents

8. If sheet is protected (ActiveSheet.ProtectContents = True) and the cell is not locked then we can clear a number

   If myRange.Locked = False Then
       myRange.MergeArea.ClearContents
   End If

9. We should set the ScreenUpdating property back to True when the macro ends

   Application.ScreenUpdating = True

Next time we assign a Hotkey to SmartDel macro.