best way to query during macro run
 

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

Than

--
Message posted from http://www.ExcelForum.com

best way to query during macro run
 

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.Del ete

End Su
-------------------

It basically sets the B column to a blank if the name doesn't exist
then deletes all rows with blanks in column B.



--
Message posted from http://www.ExcelForum.com