Macro to Delete Rows

E

Ed

I run this macro to delete unwanted rows of data that I import into excel.
If a value in
column B does not match "CR5673" the row is deleted. I now have more values
that
I want to include along with "CR5673". For example "DA2618" & "DA1131" do
not
need to be deleted.

Do I write three different if statements?

Also these values are dynamic. Can it compare the values in a defined named
range?

T.I.A.
Ed




Sub Step02()
'Delete rows with unwanted data

Dim LastRow As Long
Dim i As Long
LastRow = Range("A6536").End(xlUp).Row

For i = LastRow To 1 Step -1
If Not (Range("B" & i).Value Like "CR5673") Then
Range("B" & i).EntireRow.Delete
End If
Next 'i


End Sub
 
F

Frank Kabel

Hi
try the following. Change the line
If Not (Range("B" & i).Value Like "CR5673") Then
to
If Not (Range("B" & i).Value Like "CR5673") _
and Not (Range("B" & i).Value Like "DA2618") _
and Not (Range("B" & i).Value Like "DA1131") _
Then
 
B

Bob Phillips

Ed,

Untested, but

Sub Step02()
'Delete rows with unwanted data

Dim LastRow As Long
Dim i As Long
LastRow = Range("A6536").End(xlUp).Row

For i = LastRow To 1 Step -1
If (Not (Range("B" & i).Value Like "CR5673") And _
Not (Range("B" & i).Value Like "DA2618") And _
Not (Range("B" & i).Value Like "DA1131") Then
Range("B" & i).EntireRow.Delete
End If
Next 'i

End Sub

To test againsta named range, then try


Sub Step02()
'Delete rows with unwanted data

Dim LastRow As Long
Dim i As Long
LastRow = Range("A6536").End(xlUp).Row

For i = LastRow To 1 Step -1
If (Not (Range("B" & i).Value Like Worksheets("Sheet1").Range("cr1"))
And _
Not (Range("B" & i).Value Like Worksheets("Sheet1").Range("da1"))
And _
Not (Range("B" & i).Value Like Worksheets("Sheet1").Range("da2"))
Then
Range("B" & i).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)
 
B

Bob Phillips

Sorry, a missing ) before the Then on both versions.

--

HTH

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

Tom Ogilvy

Assume the list of values to be kept are in a defined range name named
KeepList (a range on a worksheet)

Dim LastRow As Long
Dim i As Long, bKeep as Boolean
Dim cell as Long
LastRow = Range("A6536").End(xlUp).Row

For i = LastRow To 1 Step -1
bKeep = False
for each cell in Range("KeepList")
If Ucase(Range("B" & i).Value) = Ucase(cell.value) Then
bKeep = True
Exit for
end if
Next
if not bKeep then
Range("B" & i).EntireRow.Delete
End If
Next i
 
Top