Slow search sped up by moving scroll wheel?!?

[Ed]

I have a search macro that scans each cell in a row for a text string; if
the string is not found, the entire row is hidden. Usually this moves in a
blur, so fast you can't read anything flashing on the screen. Today, it was
crawling - half a minute per row. My finger moved the scroll wheel on my
trackball, and whoosh! the lines rolled by. Left the wheel alone, and it
crawled. Back and forth on the scroll wheel, and the macro moved as fast as
I did!

Any thoughts?

Ed

 

[Jake Marx]

Hi Ed,

Not sure why this is happening - maybe the scroll is causing a screen
repaint, so you're seeing an immediate "jump" to the row the macro is
currently processing.

A few tips:

1) Use Application.ScreenUpdating=False at the beginning of your routine and
Application.ScreenUpdating=True at the end. That should help out
performance-wise.

2) Since the screen is scrolling through rows, I assume you're selecting
cells/rows in your code. There is generally no need to use Select or
Activate in your code. As an example, you can change this code:

Sheets("Sheet1").Range("A1").Select
Selection.Font.Bold=True

to this:

Sheets("Sheet1").Range("A1").Font.Bold=True

The latter will not cause the selection to change and will speed up
processing.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]