Select a row based on search criteria

Occasional Visitor

I have a series of Excel spreadsheets that have data that must be pruned before they can be used.  The format of the columns are identical in all of the worksheets.


When I open the  worksheets I have created a macro that renames the original worksheet, makes a copy of it and then renames the copy.


What I am trying to do next is to "find" a common value (it is in every worksheet),  Then select the row that the value is in, then select the remaining rows in the worksheet, delete those rows, and then save the worksheet.


I can almost make it work.  What I have figured out how to do, is create a "variable" reference for the row of the common value.  [For example:  in one worksheet the common value might be on row 30, but in another worksheet it might be on row 25 or row 19.


Can someone suggest how I can create a macro that would "find" the common value , then select the row that common value is on?  Once that row is selected, I have been able to select the remainder of the worksheet, delete those rows, save the worksheet, and close it.


The problem I am have is that when I tested the macro, it found the common value on row 24, so my macro is always finding row 24 no matter where the actual row is.


I hope I have been clear in my description.


Thank you