Getting rid of "extraneous" data (rows)
 

Option Explicit
Sub Test1()
Dim varr As Variant
Dim rng As Range
Dim rng1 As Range
Dim cell As Range
Dim i As Long
Dim bMatch As Boolean
Dim fAddr As String
varr = Array("Card Care", _
"High End Ordering", _
"Indianapolis", _
"Kansas City", _
"Mesa", _
"Minneapolis", _
"New Orleans", _
"Pittsburgh", _
"Syracuse")
Set rng = Range(Cells(2, "S"), _
Cells(Rows.Count, "S").End(xlUp))
For Each cell In rng
bMatch = False
For i = LBound(varr) To UBound(varr)
If LCase(cell) = LCase(varr(i)) Then
bMatch = True
Exit For
End If
Next
If Not bMatch Then
If rng1 Is Nothing Then
Set rng1 = cell
Else
Set rng1 = Union(rng1, cell)
End If
End If
Next
If Not rng1 Is Nothing Then
rng1.EntireRow.Delete
End If

Set rng1 = Nothing
Set rng = Columns(6).Find("UMIU")
If Not rng Is Nothing Then
fAddr = rng.Address
Do
If LCase(Cells(rng.Row, "S")) = "card care" Then
If rng1 Is Nothing Then
Set rng1 = rng
Else
Set rng1 = Union(rng, rng1)
End If
End If
Set rng = Columns(6).FindNext(rng)
Loop While Not rng Is Nothing And rng.Address < fAddr

If Not rng1 Is Nothing Then
rng1.EntireRow.Delete
End If
End If
End Sub

Test this on a copy of your data

--
Regards,
Tom Ogilvy


"JLZ" wrote in message
...
Hello everyone,

I've got an excel file that contains many entries I don't need. I'd like
to either keep just certain rows of information and/or delete other rows. I
currently have columns "A" through "U" with information. I'd like to retain
only the rows that have certain entries in column "S".

For example, currently column "S" contains the city or office name. I'd
like to keep only the rows for the following:

"Card Care"
"High End Ordering"
"Indianapolis"
"Kansas City"
"Mesa"
"Minneapolis"
"New Orleans"
"Pittsburgh"
"Syracuse"

Also, but not as important, I'd like to get rid of certain rows for the
"Card Care" office. I'd like to get rid of the "UMIU" classification. This
classification is in column "F".

Any ideas how I might be able to accomplish this? I would very much
appreciate any help I could get.