Finding the range of cells that contain specific text

Hi All,


I'm currently writing a macro that needs to search an entire worksheet for specific text - the text appears in several hundred (cells) rows.

Where this text appears, I want to copy those rows, then paste them into another spreadsheet - the copy and paste part is easy - it's determining the code (vba) to will find the exact rows (first and last) that contain the cells where this text is found that is the problem.

All help greatly appreciated.

Thanks in advance

Andrew

Hi tigeravatar,

Great bit of code!

Does the job nicely - I can now use the value (range) stored in 'rng.Copy.Address' and copy them into a new xls with code similar to that below:

Rows(rngCopy.Address).Select
Selection.Copy
Workbooks.Add
Rows("2:2").Select
ActiveSheet.Paste


Thanks!