Dim rng As Range, cell As Range, del As Range
Set rng = Intersect(Range("E2:E9000"), ActiveSheet.UsedRange)
For Each cell In rng
If application.countif( cell.Resize(1,5),"MSC") = 0 Then
If del Is Nothing Then
Set del = cell
Else: Set del = Union(del, cell)
End If
End If
Next cell
On Error Resume Next
del.EntireRow.Delete '

--
Regards,
Tom Ogilvy


"JenIT" wrote:

Hello - I am trying to create a macro that will look at data and if
there is a specific value in any one of the 4 columns...keep that row
and delete all the others. I have this code working to consider one
column...but I cannot make it work to look at multiple columns for its
selection. Here is my one column code.

' DELETE ALL BUT MSC
Dim rng As Range, cell As Range, del As Range
Set rng = Intersect(Range("G2:G9000"), ActiveSheet.UsedRange)
For Each cell In rng
If (cell.Value) < "MSC" Then
If del Is Nothing Then
Set del = cell
Else: Set del = Union(del, cell)
End If
End If
Next cell
On Error Resume Next
del.EntireRow.Delete '

I have not been able to use G$ as it then deletes my header.
To span the multiple columns looking for MSC in any one of the cells in
E, F, G, or H I have tried replacing the G2 with E2:H9000 or E$:H$ and
if I do this - nothing happens, it's like the piece of code is skipped.


Any help would be greatly appreciated, I have been frustrated with this
for a few days now.