Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Clearing Data
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Clearing Data
Just record a macro while you clear the areas you are interested in. After
that you only need to run the macro. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Clearing Data
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 "Troy2006" wrote: 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Clearing Data
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/200...e-elses-macro/ Hope this helps, Hutch "Troy2006" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
clearing data from excell sheet | Excel Discussion (Misc queries) | |||
clearing data but retaining formulas | Excel Discussion (Misc queries) | |||
Clearing Old data in Pivot Tables | Excel Discussion (Misc queries) | |||
Clearing unseen data | New Users to Excel | |||
VBA Question / Clearing out All Data | Excel Worksheet Functions |