Find a string in a range

[bbrother]

I need the fastest way to look across a range of cells where each cell in the range contains a long string to see if any words in a list occurs in any of those cells. The first thing I did of course was to read the range of cells into an array, then I loop through my list (for each item in listarray) and do a simple instr on each element of the array. All I need is a yes/no - does any list item occur in the text, but I need to repeat this for about 50,000 ranges so I need to do it quickly. For example, suppose you had a worksheet where each cell contained a paragraph of text, and you needed to check each row in the worksheet for a list of words the FCC doesn't allow (that often slip out when finding bugs). You could concatenate all of the cell values in the row into a large string, then check that large string using instr across each item in your list. However, I think it is faster to check each paragraph independently so INSTR doesn't have to slog through giant strings - because I can bail out of the check as soon as a hit is found (but then I have another nested loop to do this)

 

[Rick Rothstein]

I don't know if this will help you or not, but assuming your data started on cell A1 and that you stored your data as an array in a Variant variable named Arr, you can create a joined text string of the values in Columns A, I:K, AE:AG on any give row R using this single line of code...

JoinedText = Join(Application.Index(Arr, R, Split("1 9 10 11 31 32 33"))

You could then use the InStr function to search this joined text string for the text you are interested in. Application.Index and InStr are both quite fast and doing the above should allow you to eliminate one of your loops. Just a guess as I haven't test it, but given that, this suggestion might possibly give you a small reduction in the time it takes to run your macro.

 

[bbrother]

Rick, That is a good suggestion. I was thinking along the same lines too. By combining the strings first you only execute one instr, but it seems the overhead of very large strings eats up the lack of having to execute multiple instr operations.

What does seem to help (but is terribly wasteful of memory) is to create yet another array containing only the cells I want to search through (all the rows, but only the selected columns). The smaller array loops much faster. When I have a hit on a row I add this to a collection, so I end up with a collection identifying the rows I need. Then I loop through this collection to copy rows from the master array into yet a another new array of output containing only the rows that I want.

The lesson learned for me is that large arrays are much faster than working with cells, but still have a lot of overhead. Working on subsets of the array is much faster.

Thank you Rick for your patience and ideas!

 

[Rick Rothstein]

What does seem to help (but is terribly wasteful of memory) is to create yet another array containing only the cells I want to search through (all the rows, but only the selected columns).

You can create that sub-array directly with one line of code...

SubArray = Application.Index(Cells, Evaluate("ROW(1:" & LR & ")"), Split("1 9 10 11 31 32 33"))

where LR is the row number for the last row of data.

 

[bbrother]

Now that is cool! Thanks!