Macro to delete rows after comparing numbers in two columns

[mgalessi]

Hello,

I am scanning barcode numbers straight into excel (Column C). Column B is numbered 1-5000, in order, but Column C will not be in any order (6,15,1,19,20). What I'd like to do is compare the cells that were recently populated with scanned barcodes to the ID numbers that exist in Column B, and then delete the rows that have the same number in Column B and C. I'm currently trying to manipulate, but am not wed to :


Sub delRows()
Dim i As Long
Application.ScreenUpdating = False
For i = UsedRange.Rows.Count To 1 Step -1
If Cells(i, 3).Value = Cells(i, 2).Value Then Cells(i, 2).EntireRow.Delete Shift:=xlUp
Next i
Application.ScreenUpdating = True
End Sub

Thanks!

[stnkynts]

How is the code you supplied not working for you. From my understand of what you want, it should work just fine.

[mgalessi]

It will only delete if the numbers are lined up next to each other in two different columns. Since I'm scanning mailings, Column C will not match up with Column B because I will never get all 1-5000 mailings in at one time. So, column C is random 1-5000, and column B will be sequential 1-5000 (the first time!). After the first mailing is received, column B will not be in order anymore either since I will hopefully have deleted the mailings we have already received.

Thanks!

[stnkynts]

If there is a duplicate number, say 5 shows up in row Column B Row 3 and Column C Row 48, which row do you want to delete? Both rows?

[mgalessi]

Good question. I would like to delete the row that column c matches with b. C will be compared to B, and b is my identifier for each row.in your example, I would want to delete row 3.

[stnkynts]

Try:

Please Login or Register  to view this content.

[mgalessi]

For some reason, it's not coming up as being a macro after I paste it.

[stnkynts]

Apparently I forgot the put the top part it. See amended post above

[mgalessi]

Thanks for all your work on this issue. It kind of worked. Let me explain: I scanned 14 barcodes into column C in reverse order (from 14 to 1, so C2 was number 14, C3 #13, etc.) all the way to # 1. When I ran the macro, it successfully deleted the rows that had the ID # 8-14, but it did not delete 1-7.

[stnkynts]

Hmmm. I tried changing the lookup and last row reference which might do the trick. If this doesn't work could you upload a copy of your workbook so I analyze it.

Please Login or Register  to view this content.

[mgalessi]

It did the same thing, how do I upload?

Thanks!

[stnkynts]

Right next to the "Post Quick Reply" button you will see "Go Advanced". Under there is where you attach a workbook.

[mgalessi]

mailing list example.xlsx


Does this work? Thanks!

[stnkynts]

Ok. Having your test workbook helped me understand a little bit better what was going on. The original code would delete values in column C before it had a chance to compare to column B. This new code has an extra step which should prevent that. Run it on a copy of your workbook to make sure I have it comparing and deleting the proper columns

Please Login or Register  to view this content.

[mgalessi]

Works great! I really appreciate all of your time on this. One question, and it's not a big deal, but is there any way to erase column c entries after the macro is over? It only erases them if the row that the entry was on was deleted. For example, if you put 27 in C2, it'll delete the ID #27 row and leave 27 under C3. Like I said, it's not a big deal, we can absolutely live with this if it creates a problem.

Thanks again!

[stnkynts]

So you want to delete the entry in column C if a value is found in column B? Add in the 1 line of code and it should do it:

Please Login or Register  to view this content.