Macro/Loop If Statement Help -delete the row with the specifi
Bob - This Worked Fantastic. I didn't know you could use an AND statement
with the if too. Just one more question...please. I have more than one that
has to go thru the loop and I was wondering if I can Combine them. Here is
what I have:
'Remove AB Codes that are ETA Pending
Dim iLastRow As Long
Dim i As Long
iLastRow = Cells(Rows.Count, "I").End(xlUp).row
For i = iLastRow To 1 Step -1
If Cells(i, "I").Value = "AB" And Cells(i, "I").Interior.ColorIndex
= 6 Then
Rows(i).Delete
End If
Next i
iLastRow = Cells(Rows.Count, "I").End(xlUp).row
For i = iLastRow To 1 Step -1
If Cells(i, "I").Value = "ABPEND" And Cells(i,
"I").Interior.ColorIndex = xlNone Then
Rows(i).Delete
End If
Next i
"Bob Phillips" wrote:
Sub Test()
Dim iLastRow As Long
Dim i As Long
Dim j As Long
iLastRow = Cells(Rows.Count, "I").End(xlUp).Row
For i = iLastRow To 1 Step -1
If Cells(i, "I").Value = "AB" And _
Rows(i).Delete
End If
Next i
End Sub
--
HTH
RP
(remove nothere from the email address if mailing direct)
"Bricktop" wrote in message
...
I am trying to find all the cells in my worksheet that have "AB" in column
'I' and the AB is highlighted with an interior color = 6 .... then delete
the
row .
Here is what I have so far.
FIRSTROW = 1
Do While FIRSTROW 0
FIRSTROW = Application.Match("AB", Range("i:i"), 0)
If Range("a" & FIRSTROW).Interior.ColorIndex = 6 Then
Rows(FIRSTROW & ":" & FIRSTROW).Select
Selection.Delete
End If
'this is the part that is not working. What is happening is that when
their
is no match the code bombs so I tried to put an if statement in to change
the
firstrow to 0 if their is no match. But the if statement is not working.
If FIRSTROW = Application.Match("AB", Range("i:i"), 0) Is Nothing Then
FIRSTROW = 0
End If
Loop
|