Clear contents of user input range on workbook open

Ease of Use


Version tested with

2000, 2002, 2003 

Submitted by:



Data input in a range is used somewhere else in the workbook so the data in the input sheet is no longer needed. 


You have users inputting data in a range and then that data is added to a different sheet. They close the book and you will want the input range cleared for the next user. 


instructions for use


' Put this in the Sheet1 code module: Option Explicit Private Sub Workbook_Open() Dim ws As Worksheet, sht1 As Worksheet 'Make Sheet 1 the active sheet Set sht1 = ActiveSheet 'turn off screenupdating to cut down on screen flicker Application.ScreenUpdating = False 'Select the range(you can change this to suit your needs 'and clear the contents of the selected range ActiveSheet.Range("a2:E7").ClearContents sht1.Activate Set sht1 = Nothing 'Turn screenupdating back on Application.ScreenUpdating = True End Sub

How to use:

  1. Right mouse click on the sheet 1 tab and click on view code.
  2. Paste the code from above into the module for sheet 1
  3. Close the Visual Basic Editor by clicking on the X in the upper right hand corner of the VBE or by going to File-Close.

Test the code:

  1. Type some data into the range A2 through E7 and save the workbook
  2. Close the workbook and reopen it.
  3. The range A2 through E7 will be empty.

Sample File:

clear contents of range on open.zip 7.66KB 

Approved by mdmackillop

This entry has been viewed 160 times.

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