Keep certain rows only?

[CPower]

Hey ya doin Guys,

Could someone please tell me how to keep certain rows only, on th
basis of whats in one col of the row??

e.g want to keep all the rows that have fred, bill and terry in colum
D, but want to delete every other row that has jim, peter, 00342
blank cell, etc. in column D.

any help on this would be great,

Thanls,
Cathal

 

[Patti]

Here's one way:

Private Sub DeleteRows()

LstRow = Cells(Rows.Count, "d").End(xlUp).Row
'change to LstRow to 2 Step -1 if you have a header row
For r = LstRow To 1 Step -1
If Not Cells(r, 4) = "fred" Then
If Not Cells(r, 4) = "bill" Then
If Not Cells(r, 4) = "terry" Then
Cells(r, 4).EntireRow.Delete
End If
End If
End If
Next r

End Sub

 

[CPower]

This is great patti thanks a mil, now i need a way to delete the blan
cells, if u can help that would be great.

Thanks,
Cathal

 

[Patti]

Cathal,

Hard to say without knowing where the blank cells are and what you want to
happen once they are deleted (which way to shift cells).

One thing you can do is turn on the macro recorder to see the code that
Excel generates. For example, if you wanted to select and delete all the
blank cells in column E, turn on the macro recorder (Tools > Macro > Record
Macros), then select column E, and go to Edit > Go To > Special. Then
select "Blanks" and hit OK. Then go back to Edit and select Delete. Once
you stop the macro recorder and look at the code, you see that you get
something like this

Sub DeleteBlanks()
Columns("E:E").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.Delete Shift:=xlToLeft
End Sub

Patti