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?

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?

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?

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?

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?

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?