Macro to Delete Rows

  • Thread starter Thread starter Ed
  • Start date Start date
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
 
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
 
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)
 
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)
 
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
 
Back
Top