Detecting successful replace or searching a large sheet fast

[Tsjallie]

I have a workbook containing a sheet with over 45K rows and abt. 55 columns (workbook is over 15 MB, so can't post it).
I need to find out whether that sheet contains a certain text.

Is there a fast way to do this?

Checking each cell takes ages. So does Find.

Somewhere on the internet I ran into a suggestion to use Replace iso Find.
Trying this manually looks very promising.
What I cannot find is how to detect (in VBA) whether a replacement actually occurred (thus telling me that the sheet contains the text I'm looking for).
MSDN suggests that Replace return some boolean, but I didn't yet manage to capture anything.

Any ideas how to do this?
Or any ideas how to do the find quickly?

[AB33]

Did you use find function ONLY or find and find next functions together? Depending on the what are trying to find, you can incorporate the find,next function to speed up the execution.

[Leith Ross]

Hello

I developed this macro to provide an extremely fast search for a single column of data. Perhaps you can adapt this to your needs. The macro returns a 1-D array of the matching results.

Please Login or Register  to view this content.

Eaxample

Please Login or Register  to view this content.

[Tsjallie]

I tried the find dialogue in the sheet (not in VBA) to see if it would make difference.
Will try find next in VBA.
Thx for the quick response

[Tsjallie]

Thx Leith for your quick response
This certainly looks like addressing my problem. Will try it.
I'll let you know.

[Tsjallie]

Hi Leith,
tried your function and it's bloody fast! And so simple. I love it!
Thx a lot. Problem solved!

[Leith Ross]

Hello Tsjallie,

You're welcome.