Macro to Delete Rows
 

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

Macro to Delete Rows
 

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

--
Regards
Frank Kabel
Frankfurt, Germany

Ed wrote:
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

Macro to Delete Rows
 

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)

"Ed" wrote in message
...
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

Macro to Delete Rows
 

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)

"Bob Phillips" wrote in message
...
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)

"Ed" wrote in message
...
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

Macro to Delete Rows
 

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

--
Regards,
Tom Ogilvy


"Ed" wrote in message
...
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