VBA code to compare 2 sheets and delete rows based on 2 criteria

[tervozina]

Hello,

I have 2 sheets: 1 sheet has 4 columns, 2 sheet contains 3 columns. Column B on both sheets has document numbers. Column D on Sheet1 has document status.
So, I want to compare 2 sheets and delete rows from Sheet1 which have status (column D) "Settled" in Sheet1 plus do not match document numbers in column B from Sheet2.

I have the code to compare 2 sheets to delete rows from the Sheet1 if document number is not on Sheet2, but I cannot figure out how to incorporate second condition with "Settled" status in column D in Sheet1 to be deleted.
The point is, I want the rows in Sheet1 to remain only if they are a match to document number in Sheet2 and have any other status than "Settled", so even if they are not in Sheet2 but have a status anything else than "Settled", they should not be deleted.

Hope that makes sense?

Any help would be appreciated.

 

[Skyybot]

I'm at a loss. I can't get it to fail on my machine. Last ditch effort. Change

x = sht1.UsedRange.Rows.Count + sht1.UsedRange.Rows(1).Row - 1
y = sht2.UsedRange.Rows.Count + sht2.UsedRange.Rows(1).Row - 1

to

x = sht1.UsedRange.Rows.Count
y = sht2.UsedRange.Rows.Count

 

[tervozina]

I'm at a loss. I can't get it to fail on my machine. Last ditch effort. Change

x = sht1.UsedRange.Rows.Count + sht1.UsedRange.Rows(1).Row - 1
y = sht2.UsedRange.Rows.Count + sht2.UsedRange.Rows(1).Row - 1

to

x = sht1.UsedRange.Rows.Count
y = sht2.UsedRange.Rows.Count

Nope, doesn't work.

I believe the problem is in "Set checkRng = Range(sht1.Cells(2, 2), sht1.Cells(x, 2))" and it always highlights this line on "Debug"

 

[tervozina]

Nope, doesn't work.

I believe the problem is in "Set checkRng = Range(sht1.Cells(2, 2), sht1.Cells(x, 2))" and it always highlights this line on "Debug"

I just tried the code on a new workbook and it did work, not exactly as I wanted though. I had a document on "OP" sheet which didn't have "settled" status and was not on "Statement" sheet and it was deleted as well but I need it if even it is not on "statement" sheet and doesn't have status "Settled" not to be deleted.

I am not sure why the code does not work on my original workbook. Maybe because I am using a button to run macro. That is the only difference I see here.

 

[tervozina]

I changed "Or" to "And" and now it gives the results I want but it doesn't work with the button... Geezzz what a headache!

 

[tervozina]

I added a Form Button and then it worked
Thank you very much for your help Skyybot

 

[Skyybot]

I added a Form Button and then it worked
Thank you very much for your help Skyybot

I'm glad it finally worked for you! I was banging my head...