Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Excel: Clear all cell entries EXCEPT formulas?
Excel:
Is there any way to clear all cell entries EXCEPT formulas so that a spreadsheet that needs to have new entries every month can be cleared easily for the new month without having to work around the formulas when clearing the cells? |
#2
|
|||
|
|||
Newsgal, edit, go to, special, constants, OK, delete, or how about saving
the file as a template and open a new copy each , month? -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "newsgal" wrote in message ... Excel: Is there any way to clear all cell entries EXCEPT formulas so that a spreadsheet that needs to have new entries every month can be cleared easily for the new month without having to work around the formulas when clearing the cells? |
#3
|
|||
|
|||
Select the range of cells and run this macro:
Sub ClearAllSaveFormulas() Dim cell As Range For Each cell In Selection With cell If Not .HasFormula Then .ClearContents End If End With Next End Sub --- To run, press ALT+F11, go to Insert Module, and paste in the code above. Press ALT+Q. Now select your range of cells and go to Tools Macro Macros and run the macro. HTH Jason Atlanta, GA "newsgal" wrote: Excel: Is there any way to clear all cell entries EXCEPT formulas so that a spreadsheet that needs to have new entries every month can be cleared easily for the new month without having to work around the formulas when clearing the cells? |
#4
|
|||
|
|||
I bet you have constants that you want to keep--row and column headers,
instructions/descriptions for data entry??? If that's the case, select your range to clear and give it a nice name. (Insert|name|define) Then whenever you want to clear that range, just hit Edit|Goto (or F5 or ctrl-g) select that name (or type it in) and hit the delete key. newsgal wrote: Excel: Is there any way to clear all cell entries EXCEPT formulas so that a spreadsheet that needs to have new entries every month can be cleared easily for the new month without having to work around the formulas when clearing the cells? -- Dave Peterson |
#5
|
|||
|
|||
Hi newgal,
You can also use formulas for constants, for that matter. When I was inserting rows, the clearing of constants was my original stumbling block, I had found the rest of what I needed. You can see an example in the first macro at http://www.mvps.org/dmcritchie/excel/insrtrow.htm The purpose of the macro was to insert rows copying formulas, the soluton was to insert rows, copying entire rows, then deleting the constants. The subroutine has several interesting things in it. That web page also has variations of the macro you were already given triggered by double-click. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Dave Peterson" wrote in message ... I bet you have constants that you want to keep--row and column headers, instructions/descriptions for data entry??? If that's the case, select your range to clear and give it a nice name. (Insert|name|define) Then whenever you want to clear that range, just hit Edit|Goto (or F5 or ctrl-g) select that name (or type it in) and hit the delete key. newsgal wrote: Excel: Is there any way to clear all cell entries EXCEPT formulas so that a spreadsheet that needs to have new entries every month can be cleared easily for the new month without having to work around the formulas when clearing the cells? -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel: Clear all cell entries EXCEPT formulas?
This is almost exactly what I am looking for except...
I am using a Personal Budget spreadsheet that is perdesgined from the Template Download site. http://office.microsoft.com/en-us/te...CT011377171033. I have modified it to work the way I want it to work. I want to create a button that upon clicking will clear all cells containing entries and calculations so they can receive new data. The macro by Jason is cool but how can I make it work with a button? Also the sheet has several cell ranges that would need to be cleared without clearing the formulas the cells contain. Any ideas on how I can make this work? "Jason Morin" wrote: Select the range of cells and run this macro: Sub ClearAllSaveFormulas() Dim cell As Range For Each cell In Selection With cell If Not .HasFormula Then .ClearContents End If End With Next End Sub --- To run, press ALT+F11, go to Insert Module, and paste in the code above. Press ALT+Q. Now select your range of cells and go to Tools Macro Macros and run the macro. HTH Jason Atlanta, GA "newsgal" wrote: Excel: Is there any way to clear all cell entries EXCEPT formulas so that a spreadsheet that needs to have new entries every month can be cleared easily for the new month without having to work around the formulas when clearing the cells? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel: Clear all cell entries EXCEPT formulas?
"dvonj" wrote: This is almost exactly what I am looking for except... I am using a Personal Budget spreadsheet that is perdesgined from the Template Download site. http://office.microsoft.com/en-us/te...CT011377171033. I have modified it to work the way I want it to work. I want to create a button that upon clicking will clear all cells containing entries and calculations so they can receive new data. The macro by Jason is cool but how can I make it work with a button? Also the sheet has several cell ranges that would need to be cleared without clearing the formulas the cells contain. Any ideas on how I can make this work? "Jason Morin" wrote: Select the range of cells and run this macro: Sub ClearAllSaveFormulas() Dim cell As Range For Each cell In Selection With cell If Not .HasFormula Then .ClearContents End If End With Next End Sub --- To run, press ALT+F11, go to Insert Module, and paste in the code above. Press ALT+Q. Now select your range of cells and go to Tools Macro Macros and run the macro. HTH Jason Atlanta, GA "newsgal" wrote: Excel: Is there any way to clear all cell entries EXCEPT formulas so that a spreadsheet that needs to have new entries every month can be cleared easily for the new month without having to work around the formulas when clearing the cells? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I get top cell alignment in excel 2003 | Excel Discussion (Misc queries) | |||
How do I fix Text entries in Excel that get cut off? | Excel Discussion (Misc queries) | |||
Can I copy a combo box in Excel 2002 with a relative cell link? | Excel Discussion (Misc queries) | |||
In MS Excel, how do I fill in a column with the same cell from se. | Excel Worksheet Functions | |||
How Can I make a cell flash in Excel | Excel Discussion (Misc queries) |