Clearing cells of numbers, but not cell references.
 

I have several ranges on several sheets which contain a mixture of
a) numbers in the form of: 123
b) numbers in the form of: =123+456 (resulting in 579)
c) formulae in the form of: =sum(A1:A20)
d) formulae in the form of: =A30+A40+A50 etc..

Each month I need to clear the numbers in categories a & b, but leave
the formulae in categories c & d

What's the simplest and quickest way of achieving this?

Is it a macro which loops through all the cells testing to see whether
the cell should be cleared, or is there a more elegant solution.

If a looping macro, can someone suggest what the test should be
please?

Many thanks,

Richard Buttrey

Clearing cells of numbers, but not cell references.
 

Richard, since category b is a formula like c&d, you can't test for 'has
formula'. If the a&b types of data always appear in a certain range of cells
on each worksheet, I would take the time to write some code that would
'ClearContents' of those ranges for each sheet.

Sub Reset ()
Sheet1.Range("A1:A20,A33:A200,B2:F45").ClearConten ts
Sheet1.Columns("G:I").ClearContents
Sheet1.Rows("201:300").ClearContents
Sheet2.Range....etc
Sheet3.Range....etc
End Sub

Use code like the above to clear the cells around the c&d types you want to
keep.

Mike F

"Richard Buttrey" wrote in message
om...
I have several ranges on several sheets which contain a mixture of
a) numbers in the form of: 123
b) numbers in the form of: =123+456 (resulting in 579)
c) formulae in the form of: =sum(A1:A20)
d) formulae in the form of: =A30+A40+A50 etc..

Each month I need to clear the numbers in categories a & b, but leave
the formulae in categories c & d

What's the simplest and quickest way of achieving this?

Is it a macro which loops through all the cells testing to see whether
the cell should be cleared, or is there a more elegant solution.

If a looping macro, can someone suggest what the test should be
please?

Many thanks,

Richard Buttrey