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.

 

comments powered by Disqus