For tracking many names, I prefer something along this line. Make a Table
of your data, and give it a range name (ie "Tbl". This should make it easy
to keep updated.
Then, use Data Validation. Adjust for your own range.

Sub Demo()
Dim rng As Range

With [A1:A20].Validation
.Delete
.Add _
Type:=xlValidateList, _
Formula1:="=Tbl"

For Each rng In .Parent.Cells
If rng.Validation.Value Then rng.ClearContents
Next

.Delete ' Delete Validation
End With
End Sub

HTH :)
--
Dana DeLouis
Win XP & Office 2003


"mkingsley" wrote
in message ...

I have a 10,000 line worksheet that I have constructed a macro for and
it works great. I had another request to create another one looking
for different data. My original had this as a query component (looking
for sales reps):

Dim i As Long
Dim iStart As Long
Dim iEnd As Long

iStart = 1
iEnd = Range("B65536").End(xlUp).Row

For i = iStart To iEnd
If Range("B" & i).Value < "Claude Mossian" And _
Range("B" & i).Value < "Craig Kowal" And _
Range("B" & i).Value < "Dave Wester" And _
Range("B" & i).Value < "David Stone" And _
Range("B" & i).Value < "Elaine Ober" And _
Range("B" & i).Value < "House Account INDiana" And _
Range("B" & i).Value < "House Account Indiana Lab" And _
Range("B" & i).Value < "Jill Campbell" And _
Range("B" & i).Value < "Lana Compo" And _
Range("B" & i).Value < "Steve Horvath" Then
Range("B" & i).Value = ""
End If
Next

the new data criteria are numbers, so I modified the old macro and
entered it like this:

Dim i As Long
Dim iStart As Long
Dim iEnd As Long

iStart = 1
iEnd = Range("C65536").End(xlUp).Row

For i = iStart To iEnd
If Range("C" & i).Value < "21012" And _
Range("C" & i).Value < "21056" And _
Range("C" & i).Value < "22053" And _
Range("C" & i).Value < "23801" And _
Range("C" & i).Value < "24028" And _
Range("C" & i).Value < "24087" And _
Range("C" & i).Value < "24091" And _
Range("C" & i).Value < "24095" And _
Range("C" & i).Value < "24114" And _
Range("C" & i).Value < "24189" And _
Range("C" & i).Value < "25233" And _
Range("C" & i).Value < "25316" And _
Range("C" & i).Value < "30402" And _
Range("C" & i).Value < "34209" And _
Range("C" & i).Value < "38417" And _
Range("C" & i).Value < "38820" And _
Range("C" & i).Value < "39206" And _
Range("C" & i).Value < "39802" And _
Range("C" & i).Value < "39803" And _
Range("C" & i).Value < "39856" And _
Range("C" & i).Value < "42022" And _
Range("C" & i).Value < "45894" And _
Range("C" & i).Value < "69428" And _
Range("C" & i).Value < "70393" And _
Range("C" & i).Value < "331961" Then
Range("C" & i).Value = ""
End If
Next

I have 40 customer numbers to query, but now every time I try to add
another line, I get "too many line continuations"

Any help is appreciated.

Mike Kingsley


--
mkingsley
------------------------------------------------------------------------
mkingsley's Profile:
http://www.excelforum.com/member.php...fo&userid=4291
View this thread: http://www.excelforum.com/showthread...hreadid=377746