Delete rows based on comparing data in multiple columns with data in 1 column.

[zookeepertx]

Hello gurus!

I have a macro that works fine - for the most part. I'll put illustrations below to help with visualizing the problem. There are PO numbers in J, often more than one, separated by commas. There are also PO numbers starting in Z and continuing for an unknown number of columns - usually only 1 or 2, but some rows may have more.

I need it to compare Z in a row with J in the same row and, if the number in Z is NOT found in the string in J, color Z what I like to call "screaming green". If Z IS found in J, then look in AA. If AA is NOT found in J, then color AA screaming green but if AA IS in J, then look in AB. Continue this to the last populated cell in that row.

If ALL of the cells in that row, from Z to the last column, then delete the whole row. It doesn't matter if there are numbers in the string in J that aren't in the columns Z to the last column; that can be ignored.

The first screenshot is the data as it starts out, the second is how I need it to end up and the third is what that section of the macro currently does.
**(I couldn't get the cells in the table to have a green background, so I had to change the font color. So, anything with green font represent where I need the CELLS to get colored green)**

	I	J	Z	AA	AB
1	Product ID	P.O. In Tradeflow	Newly Uploaded PO
2	00MCE0112-00M004A|10001316	10100341888	10100341888
3	11123|10991535	10100286268	10100355841
4	11126|10991535	10100286268	10100355841
5	11127|10991535	10100286268	10100355841
6	120096|10003949	10100354437,SAMPLE	10100354437
7	191DR1019 0029|10000293	10100274652	10100357592
8	19ECE0248-19E004A|10001316	10100341942,10100341888	10100341942	10100355662	10100341888
9	19ECE0285-19E005A|10001316	10100341942,10100341888	10100354447	10100343791
10	19ECE0297-19E009A|10001316	10100341942,10100341888	10100354447	10100341888	10100357505
11	205|10000361	10100355662	10100355662
12	315001|10005213	10100357527,10100309785,10100263145,10100246653,10100215428	10100357527
13	315002|10005213	10100357527,10100309785,10100263145,10100246653,10100215428	10100357527
14	316|10000361	10100355662	10100355662
15	694|10000361	10100355662	10100355662

<tbody>
</tbody>

	I	J	Z	AA	AB
1	Product ID	P.O. In Tradeflow	Newly Uploaded PO
2	11123|10991535	10100286268	10100355841
3	11126|10991535	10100286268	10100355841
4	11127|10991535	10100286268	10100355841
5	191DR1019 0029|10000293	10100274652	10100357592
6	19ECE0248-19E004A|10001316	10100341942,10100341888	10100341942	10100355662	10100341888
7	19ECE0285-19E005A|10001316	10100341942,10100341888	10100354447	10100343791
8	19ECE0297-19E009A|10001316	10100341942,10100341888	10100354447	10100341888	10100357505

<tbody>
</tbody>

	I	J	Z	AA	AB
1	Product ID	P.O. In Tradeflow	Newly Uploaded PO
2	00MCE0112-00M004A|10001316	10100341888	10100341888
3	11123|10991535	10100286268	10100355841
4	11126|10991535	10100286268	10100355841
5	11127|10991535	10100286268	10100355841
6	120096|10003949	10100354437,SAMPLE	10100354437
7	191DR1019 0029|10000293	10100274652	10100357592
8	19ECE0248-19E004A|10001316	10100341942,10100341888	10100341942	10100355662	10100341888
9	19ECE0285-19E005A|10001316	10100341942,10100341888	10100354447	10100343791
10	19ECE0297-19E009A|10001316	10100341942,10100341888	10100354447	10100341888	10100357505
11	205|10000361	10100355662	10100355662
12	315001|10005213	10100357527,10100309785,10100263145,10100246653,10100215428	10100357527
13	315002|10005213	10100357527,10100309785,10100263145,10100246653,10100215428	10100357527
14	316|10000361	10100355662	10100355662
15	694|10000361	10100355662	10100355662

<tbody>
</tbody>

Here is the section of coding that I have right now for that procedure:

For i = Lrow To 2 Step -1
    If Len(Range("Z" & i).Value) > 0 Then
    For j = 26 To Lcol Step 1
        If Len(Cells(i, j)) > 0 Then
            If Not (InStr(Range("J" & i).Value, (Cells(i, j).Value))) Then
            Cells(i, j).Interior.Color = RGB(80, 350, 80)
            Else
            Rows(i).Delete
            End If
        End If
    Next j
    End If
Next i

No matter what I change in the code to try and fix it, it just causes something ELSE to go wrong and I'm tired of looking at it.

Can someone please figure out what I'm doing wrong?

Thank you!

Jenny

 

[Michael M]

Maybe this

Sub MM1()
Dim lc As Integer, j As Integer
Dim lr As Long, r As Long
lr = Cells(Rows.Count, "J").End(xlUp).Row
For r = lr To 2 Step -1
    If Len(Range("Z" & r).Value) > 0 Then
    For j = 26 To Cells(r, Columns.Count).End(xlToLeft).Column
    If Len(Cells(r, j)) > 0 Then
            If Not (InStr(Range("J" & r).Value, (Cells(r, j).Value))) > 0 Then
            Cells(r, j).Interior.Color = RGB(80, 350, 80)
            Else
            Rows(r).Delete
            End If
        End If
    Next j
    End If
Next r
End Sub

 

[zookeepertx]

Hey! Looks like I was on the right track but just got a little detoured, LOL!

That's very close, with just one problem. It's deleting the rows with Product ID (in column I) of 19ECE0248-19E004A and 19ECE0297-19E009A, but those 2 rows should stay. It's deleting them because, as it goes through Z to the last column on that row, it's eventually coming to a PO that has a match in J, so it's deleting the row. What it NEEDS to do is, if there's even 1 cell in the row without a match in J, then just that unmatched cell needs to turn green (which it's doing fine) and the row does not get deleted.

For example:
* On the row with the ID 19ECE0248-19E004A, when it compares Z to J, it finds a match so it deletes the row without getting to where it finds that 10100355662 (in AA) doesn't have a match in J.
* On the row with the ID 19ECE0297-19E009A, it compares Z to J, doesn't find a match, so it correctly turns Z green. But then it gets to AA, finds that it has a match in J, so it deletes the row.

I was kind of thinking that, after it turns non-matched cells green (within Z-last column), then deletes whatever rows have NO green cells within that range, but I'm sure there's a better way to do it that I'm not seeing.

BTW, I LOVE that you thought to have it use the last column on each, individual row! That'll save time, I think!

Anyway, everything else seems to be operating correctly. Do you see a way to adjust that one part?

Thanks

Jenny

 

[Michael M]

I don't have Excel at the moment, but try

Sub MM1()
Dim lc As Integer, j As Integer
Dim lr As Long, r As Long, n As Integer
lr = Cells(Rows.Count, "J").End(xlUp).Row
For r = lr To 2 Step -1
    If Len(Range("Z" & r).Value) > 0 Then
    For j = 26 To Cells(r, Columns.Count).End(xlToLeft).Column
        n = 0
    If Len(Cells(r, j)) > 0 Then
            If Not (InStr(Range("J" & r).Value, (Cells(r, j).Value))) > 0 Then
                Cells(r, j).Interior.Color = RGB(80, 350, 80)
                n = n + 1
                Else
                If n = 0 Then
                    Rows(r).Delete
                End If
            End If
        End If
    Next j
    End If
Next r
End Sub

 

[DanteAmor]

Try:

Sub Delete_Rows()
    Dim lRow As Long, lCol As Long, i As Long, j As Long
    Dim existe As Boolean
    lRow = Range("J" & Rows.Count).End(xlUp).Row
    '
    For i = lRow To 2 Step -1
        existe = True
        lCol = Cells(i, Columns.Count).End(xlToLeft).Column
        For j = 26 To lCol
            If InStr(Range("J" & i).Value, (Cells(i, j).Value)) Then
                Cells(i, j).Interior.Color = RGB(80, 350, 80)
            Else
                existe = False
            End If
        Next j
        If existe Then Rows(i).Delete
    Next i
End Sub

 

[zookeepertx]

Hi Michael,

Unfortunately, that version does the same as the one before - it deletes those same 2 lines that should stay on the "working sheet".

* On the last row - Product ID 19ECE0297-19E009A|10001316 - the first j-loop, it finds that the value in Z is not in the string in J, so it correctly turns that first cell, Z, green like it should. But on the next j-loop, it finds that the value AA is in the string in J, so it deletes the row.
* On the other row in question - Product ID 19ECE0248-19E004A|10001316 - the first j-loop finds that the value in Z is in the string in J, so it deletes the row, even though the value in AA is NOT in the string in J and should be turned green on the next j-loop.

What it needs to do there is, from Z on, if that row has ANY green cells, that row needs to not get deleted.

All else in your code works fine!

Thanks,

Jenny

 

[DanteAmor]

Do not forget to try the code in post # 5

 

[zookeepertx]

Hello, Dante,

I did try that one and there was a discrepancy. I adjusted a couple of references (incorrectly, apparently) and ran it again and now I'm stuck in an infinite loop. (That's how I know I adjusted incorrectly, LOL!)
I've tried Alt/Esc, Alt/End, Ctrl/Esc and Ctrl/End, but nothing's breaking out of the loop! It's been going for over 20 minutes now and I'm getting bored.

As I recall, your code worked pretty well, except it colored the wrong cells green. Ones that should be green weren't and ones that should have no fill were green. I THINK that was the only problem, but I'm not positive; that's why I was trying to run it again - to make sure that's all that didn't work correctly.

Jenny

 

[zookeepertx]

Hello again!

That works almost exactly right, with just one problem. The colors are getting applied "backwards". It IS deleting all of the correct rows, though!

This is how it should turn out:

	I	J	Z	AA	AB
1	Product ID	P.O. In Tradeflow	Newly Uploaded PO
2	11123|10991535	10100286268	10100355841
3	11126|10991535	10100286268	10100355841
4	11127|10991535	10100286268	10100355841
5	191DR1019 0029|10000293	10100274652	10100357592
6	19ECE0248-19E004A|10001316	10100341942,10100341888	10100341942	10100355662	10100341888
7	19ECE0285-19E005A|10001316	10100341942,10100341888	10100354447	10100343791
8	19ECE0297-19E009A|10001316	10100341942,10100341888	10100354447	10100341888	10100357505

<tbody>
</tbody>

And this is how it IS turning out:

	I	J	Z	AA	AB
1	Product ID	P.O. In Tradeflow	Newly Uploaded PO
2	11123|10991535	10100286268	10100355841
3	11126|10991535	10100286268	10100355841
4	11127|10991535	10100286268	10100355841
5	191DR1019 0029|10000293	10100274652	10100357592
6	19ECE0248-19E004A|10001316	10100341942,10100341888	10100341942	10100355662	10100341888
7	19ECE0285-19E005A|10001316	10100341942,10100341888	10100354447	10100343791
8	19ECE0297-19E009A|10001316	10100341942,10100341888	10100354447	10100341888	10100357505

<tbody>
</tbody>

(As before, the green font represents green highlighting. I just can't do the highlighting here)
I tried a couple of little changes to try to get it to do right, but I can't get it.

Thanks!

Jenny

 

[DanteAmor]

Try this:

Sub Delete_Rows()
    Dim lRow As Long, lCol As Long, i As Long, j As Long
    Dim existe As Boolean
    lRow = Range("J" & Rows.Count).End(xlUp).Row
    Range(Cells(1, "Z"), Cells(lRow, Columns.Count)).Interior.ColorIndex = xlNone
    '
    For i = lRow To 2 Step -1
        existe = True
        lCol = Cells(i, Columns.Count).End(xlToLeft).Column
        For j = 26 To lCol
            If InStr(Range("J" & i).Value, (Cells(i, j).Value)) Then
            Else
                Cells(i, j).Interior.Color = RGB(80, 350, 80)
                existe = False
            End If
        Next j
        If existe Then
            Rows(i).Delete
        End If
    Next i
End Sub