best way to query during macro run

[mkingsley]

I get a large backorder report each day, that I sort by our branch rep
name, and oldest request date of the original order. I have recorded
macro that does all of the beginning formatting stuff. What I have no
is a long list of data, that includes not only our reps, but reps fro
all over the country. I want my macro to search column B, and selec
only our 7 reps, and delete the rest. The rep is listed every time the
have a product on backorder, so John Doe may be on 5 lines of dat
today, and 12 lines of data tomorrow. EXA: I want my macro to selec
only John Doe, and delete the rows containing the data for Mary an
Tom.

Name Product Order date
John Doe widget A 5/2/04
John Doe widget B 5/15/04
John Doe widget C 5/22/04
Mary Sue Thingy A 5/12/04
Tom Tuff Thingy B 4/30/04

If you can help, you can respond directly to (e-mail address removed)

Than

 

[kkknie]

This should do it. Assumes the data starts on row 2:


Code
-------------------
Sub DeleteAllBut7()

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

iStart = 2 'Assumes data starts on row 2
iEnd = Range("B65536").End(xlUp).Row

For i = iStart To iEnd
If Range("B" & i).Value <> "Name1" And _
Range("B" & i).Value <> "Name2" And _
Range("B" & i).Value <> "Name3" And _
Range("B" & i).Value <> "Name4" And _
Range("B" & i).Value <> "Name5" And _
Range("B" & i).Value <> "Name6" And _
Range("B" & i).Value <> "Name7" Then
Range("B" & i).Value = ""
End If
Next

Range("B" & iStart & ":B" & iEnd).SpecialCells(xlCellTypeBlanks).EntireRow.Delete

End Su