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.

 

[tervozina]

What is the value of X when you hit that line?

Sorry, you lost me :D

 

[Skyybot]

When you're looking at the VBA code, if you click on View in the menu bar and select Locals Window a window that shows all of the variable values will pop up. Put your cursor in the code somewhere click and then hit F8. Just before the code executes that line that it errors on, check what the value of X is in the locals window.

 

[tervozina]

When you're looking at the VBA code, if you click on View in the menu bar and select Locals Window a window that shows all of the variable values will pop up. Put your cursor in the code somewhere click and then hit F8. Just before the code executes that line that it errors on, check what the value of X is in the locals window.

Value is 0 for i, x and y

 

[Skyybot]

Try changing the line
set wkbk = ThisWorkbook to set wkbk = Workbooks("YourWorkbookName")

 

[Skyybot]

Try changing the line
set wkbk = ThisWorkbook to set wkbk = Workbooks("YourWorkbookName")

It needs to be the full name. i.e.- WorkbookName.xlsm (or whatever your file extension is)

 

[Skyybot]

It needs to be the full name. i.e.- WorkbookName.xlsm (or whatever your file extension is)

And always remember to save an unmolested version of your Workbook in case the code gives unwanted results.

 

[tervozina]

And always remember to save an unmolested version of your Workbook in case the code gives unwanted results.

Oh I do, I have like 7 drafts now lol

Thank you, I will try in a minute

 

[tervozina]

It needs to be the full name. i.e.- WorkbookName.xlsm (or whatever your file extension is)

Still fails. It highlights below on Debug

Set checkRng = Range(sht1.Cells(2, 2), sht1.Cells(x, 2))

 

[Skyybot]

Maybe it has something to do with the formatting on this site. I have included the code again with a VBA format for copy and paste. Maybe??

Sub compareData()
Dim sht1 As Worksheet, sht2 As Worksheet
Dim wkbk As Workbook
Dim checkRng As Range, searchRng As Range
Dim i As Integer, x As Integer, y As Integer

Set wkbk = Workbooks("WorkbookName")
Set sht1 = wkbk.Worksheets("Sheet1Name")
Set sht2 = wkbk.Worksheets("Sheet2Name")

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

Set checkRng = Range(sht1.Cells(2, 2), sht1.Cells(x, 2))
Set searchRng = Range(sht2.Cells(2, 2), sht2.Cells(y, 2))

i = x - 1

Do Until i = 0
   If searchRng.Find(checkRng.Cells(i, 1).Value) Is Nothing Or sht1.Cells(i + 1, 4).Value = "Settled" Then
        sht1.Rows(i + 1).Delete
    End If
    i = i - 1
Loop

End Sub

 

[tervozina]

Maybe it has something to do with the formatting on this site. I have included the code again with a VBA format for copy and paste. Maybe??

Sub compareData()
Dim sht1 As Worksheet, sht2 As Worksheet
Dim wkbk As Workbook
Dim checkRng As Range, searchRng As Range
Dim i As Integer, x As Integer, y As Integer

Set wkbk = Workbooks("WorkbookName")
Set sht1 = wkbk.Worksheets("Sheet1Name")
Set sht2 = wkbk.Worksheets("Sheet2Name")

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

Set checkRng = Range(sht1.Cells(2, 2), sht1.Cells(x, 2))
Set searchRng = Range(sht2.Cells(2, 2), sht2.Cells(y, 2))

i = x - 1

Do Until i = 0
   If searchRng.Find(checkRng.Cells(i, 1).Value) Is Nothing Or sht1.Cells(i + 1, 4).Value = "Settled" Then
        sht1.Rows(i + 1).Delete
    End If
    i = i - 1
Loop

End Sub

Unfortunately, it does not want to work