Good Morning,
What I think your trying to ask me is how do determine the range of the
search list if it expands or contract on Sheet1. The way I wrote the code
automatically determines the size of the range for you, ie using this line
code:
Set rng1 = ws1.Range("A1:A" & Range("A1").End(xlDown).Row)
Excel starts at Cell A1 and Searchs Downward in Column A until finds the
first empty cell, thus determine the last row of the range. So if you to
the add or delete rows from the bottom of your range the code will always
determine lastrow of the range for you. Let me caution you, do not leave
empty cells in Column A, when excel finds the first empty cell it things it
at the end of the range. Thus giving a incorrect range size.
If you noticed I remarked out the line of : 'Set rng1 =
ws1.Range("A1:A50"), I let excel determine the range list size in line code
below it. The same line code I mentioned above. I hope this answered your
questiion. Good Luck on your project....

Rick, (Fbks, AK)


"*******_kestrel" wrote in message
oups.com...
Brilliant - Thanks for your help folks !

Quick question Rick - If I wanted to expand or reduce the search range
on Sheet 1 (currently A1:A50) how would I alter it in your example ?

Cheers