Macro/Loop If Statement Help -delete the row with the specific te
 

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

Macro/Loop If Statement Help -delete the row with the specific te
 

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

Macro/Loop If Statement Help -delete the row with the specific te
 

try this:

a=1
while a < numberofrows
If cells(a,numcol).Interior.ColorIndex = 6 and cells(a,numcol)="AB" Then
rows(a).delete
a=a+1
else if
a=a+1
end if
wend

remember to change numcol to the number of the column were your AB's are and
numberofrows to the number of the last row used

"Bricktop" wrote:

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

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

Macro/Loop If Statement Help -delete the row with the specifi
 

You can have Or as well

'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) Or _
(Cells(i, "I").Value = "ABPEND" And _
Cells(i, "I").Interior.ColorIndex = xlNone) Then
Rows(i).Delete
End If
Next i


--

HTH

RP
(remove nothere from the email address if mailing direct)


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

Macro/Loop If Statement Help -delete the row with the specific te
 

I tried the following:
Range("U2:U" & LASTROW).FormulaR1C1 =
"=IF(SEARCH(""pen"",RC[-2])0,2,IF(SEARCH(""can"",RC[-2])0,2,IF(SEARCH(""hol"",RC[-2])0,2,1)))"
and this doesn't work either. Can you not do a multiple search?? I tried
just putting one with both of the if statements and that doesn't work either.
Any other suggestions?


"Bricktop" wrote:

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