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:
- Turn screen updating off to speed up our macro
Application.ScreenUpdating = False
- Declare variable as an Range
Dim selRange As Range
- 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)
- If intersection of two ranges are empty then stop the macro
If selRange Is Nothing Then GoTo exit_SmartDel End If
- Loop for each cell in the range
For Each myRange In selRange
- 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
- Method MergeArea allows us to work with merged cells too
- 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
- 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.