Clearing Data

[Troy2006]

I have absolutely no knowledge on Macros, but maybe I can still get some
help. I'm pretty good with creating general formulas, but I have never tried
to make a macro. I need to some way to enable a button on an Excel 2003 sheet
that once pressed, all the values in the predetermined cell locations are
deleted. For example, a sheet that is used every hour and saved but on the
first hour of the next day, this sheet is saved as a different name. Once
this occurs, then the name of the original sheet has values in cells from the
previous day. I need to have these values deleted so the current day can get
started without having to go through and erase cells one by one and not
messing with the cells that have formulas. I can't simply protect the cells
that have the formulas then do manual deletions, because too tedious and I
have outrageously novice employees using the sheet.

 

[TomPl]

Just record a macro while you clear the areas you are interested in. After
that you only need to run the macro.

 

[Mike H]

Hi,

it much depends how the data to clear are arranged. If it's all in one block
then you could select it and have a button that runs this Macro

Sub Clearit()
Selection.ClearContents
End Sub

or if it's in columns/rows do it like this
Sub clearit()
Range("a1:A100").ClearContents
End Sub

or another way is to clear all constants

Sub clearit1()
Selection.SpecialCells(xlCellTypeConstants, 3).ClearContents
End Sub


Mike

 

[Tom Hutchins]

Here is one way. Lock all the cells on the sheet except the cells you want
cleared. Then you can use this macro to delete all data in unlocked cells...

Sub Clearem()
Dim c As Range
For Each c In ActiveSheet.UsedRange
If c.Locked = False Then
c.ClearContents
End If
Next
End Sub

Paste the code in a VBA module in your workbook. Run it from your worksheet
by selecting Tools >> Macro >> Macros >> Clearem >> Run (or you could call
the macro from a commandbutton on your worksheet).

If you are new to macros, this link to Jon Peltier's site may be helpful:
http://peltiertech.com/WordPress/2008/03/09/how-to-use-someone-elses-macro/

Hope this helps,

Hutch