Working with finance and accounting professional the majority of my career, I see a lot of spreadsheet “templates” that are reused for multiple budget passes or monthly forecasting processes (any repatative process). When the workbooks have a number of worksheets, and they are large, it can be extremely tedious to clear out the old data and get back to a fresh, empty shell. The script below can be executed on any worksheet to clear out all the numeric values and cell comments. It ignores cells with dates, formulas, or text values.
'loop through each cell in the range of cells used in the worksheet For Each c In ActiveSheet.UsedRange.Cells 'If the cell value is null, don't do anything If Not IsNull(c.Value) Then 'Do not execute on formulas or non numeric values If Not c.HasFormula And IsNumeric(c.Value) Then 'If a cell comment exists and it is not equal 'to "KEEP", set the value of the cell to null If Not (c.Comment Is Nothing) Then If c.Comment.Text <> "KEEP" Then c.Value = Null ElseIf c.Comment Is Nothing Then c.Value = Null End If End If End If 'Execute on all cells in the range where the cell has a comment If Not (c.Comment Is Nothing) Then 'If the comment is equal to "KEEP", don't delete the comment If c.Comment.Text <> "KEEP" Then c.Comment.Delete End If Next c MsgBox "Complete"
Breaking It Down
The outside loop will loop through each cell using the ActiveSheet.UsedRange.Cells. This function will get the range of cells on the worksheet that has been used. UsedRange will take the equivalent range of using CTRL-HOME to get the upper left cell and CTRL-END to get the bottom right of the range.
For Each c In ActiveSheet.UsedRange.Cells
Each cell will be checked to verify that the value is not blank, is not a formula, and is numeric (not text). If this criteria is true, the value will be set to nothing.
If Not IsNull(c.Value) And Not c.HasFormula And IsNumeric(c.Value) Then
c.Value = Null
If the cell has a cell comment, it will be removed as well.
If Not (c.Comment Is Nothing) Then
In the full example, some additional lines are added to ignore clearing any cell with a cell comment of “KEEP”.
How To Use
To use this script, it must be added to a module. The easiest way to do this is to create a macro and associate a CTRL-? key to it.
In Excel 2007, select the Developer ribbon and click the Record Macro button. Immediately click the Stop Recording button. This will create a function in a new module for you. If the Developer tab is not visible, click the Office Button and click the Excel Options button. On the Popular tab, select Show Developer Ribbon.
In Excel 2003, select the Tools / Macro / Record New Macro menu. Immediately click the Stop Recording button.
After opening Visual Basic in Excel, expand the spreadsheet in the Project window. Expand the Modules tree and open the module. Inside the module will be a procedure that is empty. Paste the script inside the procedure. This can now be accessed by the CTRL-? that was assigned.
I will be posting more scripts like this. If you find this helpful, add your email to our mailing list near the top of the right sidebar. You will get an email any time we add a new article!