Delete Rows help?

[jaydywan]

The following macro deletes all rows on the active worksheet
' that have 1034, 1035, 1037 column E. I am looking for someone to
let me know how to do the opposite, delete all rows EXCEPT that that
have 1034, 1035, 1037 column E. Thanks.

Sub Delete_Rows()
' This macro deletes all rows on the active worksheet
' that have 1034, 1035, 1037 column E.
Dim rng As Range, cell As Range, del As Range
Set rng = Intersect(Range("E:E"), ActiveSheet.UsedRange)
For Each cell In rng
If (cell.Value) = "1034" _
Or (cell.Value) = "1035" _
Or (cell.Value) = "1037" Then
If del Is Nothing Then
Set del = cell
Else: Set del = Union(del, cell)
End If
End If
Next
On Error Resume Next
del.EntireRow.Delete
End Sub

 

[Bernie Deitrick]

Jay,

Replace

If (cell.Value) = "1034" _
Or (cell.Value) = "1035" _
Or (cell.Value) = "1037" Then


with

If (cell.Value) <> "1034" _
And (cell.Value) <> "1035" _
And (cell.Value) <> "1037" Then


HTH,
Bernie
MS Excel MVP

 

[marbarru]

Using boolean logic this must be work...

If (cell.Value) <> "1034" and (cell.Value) <> "1035" and
(cell.Value) <> "1037" Then

 

[Dave Peterson]

Maybe just reversing the logic:

Sub Delete_Rows()
' This macro deletes all rows on the active worksheet
' that have 1034, 1035, 1037 column E.
Dim rng As Range, cell As Range, del As Range
Set rng = Intersect(Range("E:E"), ActiveSheet.UsedRange)
For Each cell In rng
If (cell.Value) = "1034" _
Or (cell.Value) = "1035" _
Or (cell.Value) = "1037" Then
'do nothing
else
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
on error goto 0
End Sub

I would think you'd want to check for numbers:

If (cell.Value) = 1034 _
Or (cell.Value) = 1035 _
Or (cell.Value) = 1037 Then
'do nothing
else
....


And sometimes, it's easier to use "select case" instead of a bunch of or's:
....
select case cell.value
case is = 1034, 1035, 1037
'do nothing
case else
If del Is Nothing Then
Set del = cell
Else
Set del = Union(del, cell)
End If
end select
....

It can make it easier to read/understand.

 

[jaydywan]

Jay,

Replace

If (cell.Value) = "1034" _
Or (cell.Value) = "1035" _
Or (cell.Value) = "1037" Then

with

If (cell.Value) <> "1034" _
And (cell.Value) <> "1035" _
And (cell.Value) <> "1037" Then

HTH,
Bernie
MS Excel MVP

thanks!