Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Zeroing out data but leaving formulas.
I have a spreadsheet that I calculates monthly data and puts it into a year
end management spreadsheet. How do I clear the cells of data (zero out) but leave the formulas intact so that I can use the spreadsheet again in the comming year? |
#2
|
|||
|
|||
You should be able to just delete the static data (the data that the formulas
are using to make calculations). To toggle the formula view (see the formulas) use Ctrl+~ "clemrogan" wrote: I have a spreadsheet that I calculates monthly data and puts it into a year end management spreadsheet. How do I clear the cells of data (zero out) but leave the formulas intact so that I can use the spreadsheet again in the comming year? |
#3
|
|||
|
|||
First enter this small macro:
Sub keepform() Dim R As Range For Each R In Selection If IsFormula(R) Then Else R.Clear End If Next End Sub Next selection the area in the worksheet you want to clear and run the macro. The formulae will be left alone and everything else in the area will be cleared. -- gsnu001 Gary''s Student "clemrogan" wrote: I have a spreadsheet that I calculates monthly data and puts it into a year end management spreadsheet. How do I clear the cells of data (zero out) but leave the formulas intact so that I can use the spreadsheet again in the comming year? |
#4
|
|||
|
|||
Sorry,
This did not work. Getting "compile error" sub or function not defined and the IsFormula is highlighted Also is their any way to apply it to a whole workbook (only the unprotected cells) with 12 sheets (one for each month of the year) or do I have to go sheet by sheet? "Gary''s Student" wrote: First enter this small macro: Sub keepform() Dim R As Range For Each R In Selection If IsFormula(R) Then Else R.Clear End If Next End Sub Next selection the area in the worksheet you want to clear and run the macro. The formulae will be left alone and everything else in the area will be cleared. -- gsnu001 Gary''s Student "clemrogan" wrote: I have a spreadsheet that I calculates monthly data and puts it into a year end management spreadsheet. How do I clear the cells of data (zero out) but leave the formulas intact so that I can use the spreadsheet again in the comming year? |
#5
|
|||
|
|||
You could just record the macro instead of trying to write it. Start
recording, highlight the cells you want to delete, then press delete. You can either keep going (on to the next tab) while still recording, or you can stop, go into the editor and copy & paste what you just recorded, changing the tab names as you go. "clemrogan" wrote: Sorry, This did not work. Getting "compile error" sub or function not defined and the IsFormula is highlighted Also is their any way to apply it to a whole workbook (only the unprotected cells) with 12 sheets (one for each month of the year) or do I have to go sheet by sheet? "Gary''s Student" wrote: First enter this small macro: Sub keepform() Dim R As Range For Each R In Selection If IsFormula(R) Then Else R.Clear End If Next End Sub Next selection the area in the worksheet you want to clear and run the macro. The formulae will be left alone and everything else in the area will be cleared. -- gsnu001 Gary''s Student "clemrogan" wrote: I have a spreadsheet that I calculates monthly data and puts it into a year end management spreadsheet. How do I clear the cells of data (zero out) but leave the formulas intact so that I can use the spreadsheet again in the comming year? |
#6
|
|||
|
|||
I am sorry. Here is the missing part:
Function IsFormula(cell As Range) As Boolean IsFormula = cell.HasFormula End Function Using this would require a sheet by sheet approach -- Gary''s Student "clemrogan" wrote: Sorry, This did not work. Getting "compile error" sub or function not defined and the IsFormula is highlighted Also is their any way to apply it to a whole workbook (only the unprotected cells) with 12 sheets (one for each month of the year) or do I have to go sheet by sheet? "Gary''s Student" wrote: First enter this small macro: Sub keepform() Dim R As Range For Each R In Selection If IsFormula(R) Then Else R.Clear End If Next End Sub Next selection the area in the worksheet you want to clear and run the macro. The formulae will be left alone and everything else in the area will be cleared. -- gsnu001 Gary''s Student "clemrogan" wrote: I have a spreadsheet that I calculates monthly data and puts it into a year end management spreadsheet. How do I clear the cells of data (zero out) but leave the formulas intact so that I can use the spreadsheet again in the comming year? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Purge functions leaving only calculated data? | Excel Worksheet Functions | |||
Can Excel recognize when data is entered and apply formulas? | Excel Worksheet Functions | |||
Pasting data without removing formulas | Excel Discussion (Misc queries) | |||
Line Graph Data Recognition | Charts and Charting in Excel | |||
Formulas in source data | Charts and Charting in Excel |