find and Replace method for quick calculation not working

[WardS]

Hi everyone

I'm on Excel 2007 and have a spreadsheet that takes about 35 minutes to recalculate the present worksheet using Shift+F9.

I was having success with using find and replace when altering a formula in a column e.g. if the forumla was A2*A3 I'd make it A2*A3&53, copy it down the column, then do a find all and replace all to get rid of the '&53'. This would recalc the column in about 4 minutes all up - a lot better than half an hour.

Yesterday this method starting having a hiccup - the first 200 cells or so would be recalculated quickly, then it would start jumping around to other columns, slowing down to a crawl. The other columns didn't have the text '&53'.

Has anyone used this method to recalc and had the same issue arise?

I'm not in a position to use the normal methods of speeding up calculation (faster pc, group individual calcs into super formulae, cut down number of range names).

Regards

Ward

 

[WhoCanDo]

Do you have this workbook shared?

We have a shared workbook and we find that all the users changes are stored within the file as we go.

The only way we know of reducing the size of the file and speed up recalc is to copy the worksheet, delete the original and then rename the copy.

We do this about one a month.

 

[WardS]

The workbook isn't shared. I do a File>Save As... (to a new name) a few times each day.

Ward

 

[WhoCanDo]

Have you checked your private message lately?