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 146 times.

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