Slow Find and Replace in Excel 2007

[YANKRAY]

I am using Excel 2007 and am doing a Find and Replace of about 60 columns X 3300 rows.

It is taking a very long time to execute the Find and Replace. It takes about 4 hours.

Does anyone know if this is a setting or something I can change in Excel 2007 to speed this up or is it just my processor speed.

Thanks,
Ray

 

[Gavona]

I am not using 2007 but that does sound awful. I would suggest setting calculation to manual and seeing how that affects the time.

Are many replacements made? Are the cells changed referenced by formulae in other cells? Do you have many formulae in the workbook, or use Indirect or user defined functions?

Gavin

 

[YANKRAY]

Thanks for the response Gavin.

I have turned the calculation to manual and that did not help the matter.

The cells I am replacing the data with are all linked to other documents and every cell across the range I mentioned gets two changes per cell (so there are a lot).

I did find that I can change the 1st row I want to make the replacement to and then drag it down to where the linking ends. This is very fast. Only problem with that is I have to do it 3 times per file. But that only takes about 5 minutes. Small price to save hours.

Thanks again.
Ray

 

[Gavona]

1. Open all linked workbooks.
2. If your operation is changing the workbook linked-to then:
a. open that workbook also
b. use (Excel2003 but their must be a 2007 equivalent) Edit, Links change source rather than edit-replace.


Gavin