Excel

Clear Data Entry Cells

Ease of Use

Easy

Version tested with

2000, 2002 

Submitted by:

Anne Troy

Description:

Clear the cells on which your formulas work. 

Discussion:

You have a form that you use regularly. Perhaps you fill in data, then print it, and don't need to save a copy of the file. So, all you want to do is clear the data that you've entered each time. This method helps you do that. 

Code:

instructions for use 

			
Option Explicit 
Sub ClearEntries() 
    On Error GoTo noName 'error handler
     'you can use the word "numbers" or any other range name in the next line
    Application.Goto Reference:="numbers" 
    Selection.ClearContents 
    Range("A1").Select 
    Exit Sub 
noName: 'only fires on an error
    MsgBox "The named range you are calling does not exist." & vbCr & _ 
    "You need to name a range as 'numbers' before proceeding.", _ 
    vbOKOnly + vbInformation, "Named range required" 
End Sub

How to use:

  1. Select all the cells in which people enter their data.
  2. Choose Insert-Name-Define and type "numbers" without the quotes.
  3. Copy the code above.
  4. Hit Alt+F11 to open the Visual Basic Editor (VBE).
  5. On the left, choose your file, then hit Insert-Module from the menu.
  6. Paste the code into the code window that appears at right.
  7. Hit the save diskette and close the VBE.
 

Test the code:

  1. Hit Tools-Macro-Macros and double-click on ClearEntries.
 

Sample File:

ClearEntries.zip 7.29KB 

Approved by mdmackillop


This entry has been viewed 156 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express