VBA - Range.Find incredibly slow with xlValues compared to xlFormulas, even with no formulas in range

[relaxok]

Hi -

I'm working with a large-ish (~9000 row) spreadsheet and there is VBA code that uses it and runs Range.Find on entire columns in the sheet.

Using .Find on the column with xlValues takes about 40 seconds to find the cell, whereas using xlFormulas is virtually instantaneous. This would make sense if the range (and maybe the sheet?) had heavy use of formulas in it, but it doesn't have ANY formulas. Also, Application.Calculation is set to xlCalculationManual so it should not be recalculating anything anyway. I noticed the screen seems to flash several times while running the process with xlValues, almost like it's reloading the same document multiple times. It does not do this with xlFormulas.

This makes a process take 2 hours with xlValues and a couple minutes with xlFormulas.

Has anybody else had this happen and can recommend anything? I'm stumped.

Thanks!

 

[RoryA]

Why are you using xlValues if there are no formulas to worry about? Using xlValues has to look at the formatted values, which is a slower operation.

 

[relaxok]

Why are you using xlValues if there are no formulas to worry about? Using xlValues has to look at the formatted values, which is a slower operation.

Thanks for the question, I guess I should have been more clear about this. This is a process run on columns that WILL sometimes have formulas but in this test case doesn't.

I have always used xlValues for .Find and it's never been slow like this. My recollection is searching by values in a range should be slightly slower but not ~30 seconds for a 9000 cell search vs ~0.1 seconds. So I was hoping there's something else I can be looking for that might be slowing it down.

 

[RoryA]

Is there any conditional formatting on the sheet? Or any linked pictures in the workbook?

 

[relaxok]

Is there any conditional formatting on the sheet? Or any linked pictures in the workbook?

No pictures and no conditional formatting in the range or on the sheet.

Currently changed .Find to xlFormulas (and explained caveats) to not slow work down while I investigate what it could be.