Removing Data From Excel Templates

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
…
Next c

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
End If

If the cell has a cell comment, it will be removed as well.

If Not (c.Comment Is Nothing) Then
c.Comment.Delete
End If

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!