Here's a different approach.

Started with the sample workbook you posted.
Kept your sheet of data and userform.
For each cell in the TestRng, split the cell contents into an array of individual words as separated by spaces.
Checks for array elements being same as SearchText.

Using the same 'the' as what to check for
takes about 10 seconds and returns 3090 records case insensitive and 2814 records case sensitive.

Appears to me your challenge will be determining how many records should be returned in order to know if anything works correctly.

Attached Files

Extract_Rows_containing_word.xlsm (267.9 KB, 14 views)