Hi ...

We only want to mail advertising to individuals, not businesses. Here is
a short macro I wrote to eliminate obvious businesses from the list:

Sub Eliminate()
Dim I As Long
Dim FullName As String

For I = 2 To 500
FullName = Worksheets(1).Cells(I, 4).Value
If InStr(2, FullName, " COMPANY", 1) 0 Then
Worksheets(1).Cells(I, 1).Select
Selection.EntireRow.Delete
End If
Next I
End Sub

It worked ... Kind of. When it came to a record with `Company' in the
name it deleted it. However, if the next record also had `Company' in the
name, it was not deleted. I guess that when it eliminated record seven,
record eight became record seven, and it was done with seven. It went on to
eight, which was nine until a second ago.

I fixed it, though. I added one line:

Sub Eliminate()
Dim I As Long
Dim FullName As String

For I = 2 To 500
FullName = Worksheets(1).Cells(I, 4).Value
If InStr(2, FullName, " COMPANY", 1) 0 Then
Worksheets(1).Cells(I, 1).Select
Selection.EntireRow.Delete
I = I - 1
End If
Next I
End Sub

Now I is not incremented - Program checks record seven again. Works fine.
but there are two things I don't like.

1) Instead of `For I = 2 To 500,' I would the loop to just automatically run
until every record is checked:
`For I = 2 To EndOfColumn.'
There must be way to do that, but I don't know what it is.

2) I don't like screwing with I. I'm just an amateur, but I bet `real'
programmers never alter the value of a loop counter inside the loop itself.
(Do they??)

Sam
--
A man who had lately declared
That property ought to be shared,
Thought it going too far
When they called for his car,
And a list of exceptions prepared.

Thomas Thorneley,
From The Penguin
Book Of Limericks