Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro to keep specific name colomns and delete all others | Excel Discussion (Misc queries) | |||
Macro to delete rows containing specific data | New Users to Excel | |||
delete row contains specific word in an macro | Excel Discussion (Misc queries) | |||
Macro to delete specific rows | Excel Programming | |||
Warning Statement to Delete Macro | Excel Programming |