looping through range and deleting cell contents.

  • Thread starter Thread starter Andy Healey
  • Start date Start date
A

Andy Healey

Dear all

I'm using excel 97 + a variety of operating systems,

I have a rather large workbook which is full of 64 sheets of formulas, a lot
of the formulas are redundant one month but might be need the following
month. as such the file is over 15 MB in size.

What I have done in order to limit the size, is to put the formulas in row 1
and then if that sheet is need to paste the formula to the relevant cells
(2600 on each sheet.) but a lot of them are still not needed.

What I would like to do is then copy the cells and paste the value into
them, but whenever I try this excel crashes.
(Strangely enough if I try it on excel xp at home it works fine.)

The only work around I can think of is to have a macro to go through the
pasted range and if the value of the formula is zero to change it to zero
rather than have a long formula in there.

any ideas how to go about it, as I'm getting all confused between the
various nows, then and ifs.

many thanks

Andy
 
Dim rng as Range
Dim sh as Worksheet
for each sh in ThisWorkbook.Worksheets
set rng = sh.Range("A1").CurrentRegion
set rng = rng.offset(1,0).Resize(rng.rows.count-1)
rng.Formula = rng.Value
Next

Converts the formulas to values except in the first row.
 
Back
Top