Sam,

When deleting rows it is better to one of two things
- delete from the bottom up
- build up a range of rows to delete and delete them all at the end

Here is your code modified for the first, with automatic detection of the
end

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

Set oWS = Worksheets(1)
For I = Cells(Rows.Count,"A").End(xlUp).Row To 2 Step -1
FullName = oWS.Cells(I, 4).Value
If InStr(2, FullName, " COMPANY", 1) 0 Then
oWS.Cells(I, 1).EntireRow.Delete
End If
Next I

End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Sam" wrote in message
ink.net...
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