I’ve constructed macros that search a user range on a worksheet and looks for whole word matches (one provides case sensitive and the other insensitive searches with the option compare statements). It extracts the matches it finds to a new workbook. It determines the whole word match by testing whether the search text in the range is preceded or followed by a non-letter or non-number.



It runs very well if there are a small number of hits, even if the search range is several thousand rows, but slows down if it finds a lot of hits, probably because it loops through the ASC characters for the front and back of the search hit. For example, with a range of about 5800 rows of data, it took just over 2 minutes to extract 455 hits. But searching the same range for a different criterion that had only 14 hits took around 15 seconds.

Does anyone have a suggestion for speeding this code up when the hit count is high? Macro is attached in text file.

Attached Files

Find_Whole_Word_CS.txt (3.4 KB, 7 views)