Maximum criteria??

I have a spreadsheet that I cull data from based on this routine:

 
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 <> "Dave Wester" And _
    Range("B" & i).Value <> "Elaine Ober" And _
    Range("B" & i).Value <> "Greg Trujillo" And _
    Range("B" & i).Value <> "House Account INDiana" And _
    Range("B" & i).Value <> "Jill Campbell" And _
    Range("B" & i).Value <> "Lana Compo" Then
    Range("B" & i).Value = ""
   End If
Next

Because I only want those sales reps indicated.

Recently someone asked me if I could take the same raw data set, and select mulitple customer ship to account #'s. (and all of the data that goes with them)
(there over 75 of them). Is there any easier way to write the macro code, and are there limitations to the number of selection criteria I can use in this routine?
And the ship to #'s are not in sequence, they're all over the place.

Mike.

Originally Posted by mkingsley

I have a spreadsheet that I cull data from based on this routine:

 
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 <> "Dave Wester" And _
    Range("B" & i).Value <> "Elaine Ober" And _
    Range("B" & i).Value <> "Greg Trujillo" And _
    Range("B" & i).Value <> "House Account INDiana" And _
    Range("B" & i).Value <> "Jill Campbell" And _
    Range("B" & i).Value <> "Lana Compo" Then
    Range("B" & i).Value = ""
   End If
Next

Because I only want those sales reps indicated.

Recently someone asked me if I could take the same raw data set, and select mulitple customer ship to account #'s. (and all of the data that goes with them)
(there over 75 of them). Is there any easier way to write the macro code, and are there limitations to the number of selection criteria I can use in this routine?
And the ship to #'s are not in sequence, they're all over the place.

Mike.

Hi.

I guess the easy way is to lose the loop.

The attached (run the Macro) shows a method with no line-by-line loop but does use a 'helper' column F

The required names are entered on sheet2, the VLookup is entered via VB code, and the Filter selects non-blank rows to move to sheet3.

You can copy the header row and remove column F after checking that the code works for you.

hth
---