Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
looping through range and deleting cell contents.
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
looping through range and deleting cell contents.
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. -- Regards, Tom Ogilvy Andy Healey wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I delete the contents of a cell without deleting the formu | Excel Discussion (Misc queries) | |||
Extracting/deleting select data from range of cell | Excel Discussion (Misc queries) | |||
alert before deleting contents of a cell | Excel Discussion (Misc queries) | |||
Find cell contents in range | Excel Discussion (Misc queries) | |||
Copy contents of Range to other cell | Excel Programming |