asked on

Find rows that contain one of 6 variations of specific character string and paste those rows into another worksheet

Sheet 1 contains 54,000 rows of text and/or numbers. I am looking for a macro that will find those rows that contain any of the variations of the 6 RCO strings outlined below and copy those rows that meet this criteria into Sheet 2. The RCO string could be located anywhere within the row.

Variations of RCO string:
"-RCO"
"- RCO"
"– RCO"
"–RCO"
"—RCO"
"— RCO"

Thanks,
Andrea

SOLUTION

Glenn Ray

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Glenn Ray

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Andreamary

ASKER

Hi Glenn,

Thanks very much for both the UDF and the macro. I had success with the UDF, but the macro is giving me a run-time error '13', Type mismatch, on line 16:

If InStr(1, cl.Value, arrRCO(x), vbTextCompare) > 0 Then

It would be nice to have the macro option if possible.

Thanks,
Andrea

Andreamary

ASKER

Hi Glenn,

On further investigation, I found I had a few rows that contained "#NAME?" that were causing the problem and preventing the macro from being able to run. Once I deleted those offending rows, the macro worked perfectly.

Thanks so much for the quick and thorough solutions!

Cheers,
Andrea

Glenn Ray

Glad I was able to help.

Regards,
-Glenn