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

tervozina

New Member
Joined
Feb 18, 2023
Messages
14
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Can you submit your code for inspection?
 
Upvote 0
Can you submit your code for inspection?
Dim a As Variant, b As Variant, r As Range, lr As Long, i As Long
Dim sh As Worksheet, dic As Object

Set sh = Sheets("OP")
Set dic = CreateObject("Scripting.Dictionary")
dic.CompareMode = vbTextCompare

lr = sh.Range("B" & Rows.Count).End(xlUp).Row
a = sh.Range("B1:B" & lr).Value2
b = Sheets("Statement").Range("B1", Sheets("Statement").Range("B" & Rows.Count).End(xlUp)).Value2

Set r = sh.Range("B" & lr + 1)
For i = 1 To UBound(b)
dic(b(i, 1)) = Empty
Next
r.EntireRow.Delete
 
Upvote 0
I got this to work. I'm assuming that your data has headers. Hope this helps.

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 = ThisWorkbook
Set sht1 = wkbk.Worksheets("Sheet1")
Set sht2 = wkbk.Worksheets("Sheet2")

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
 
Upvote 0
I got this to work. I'm assuming that your data has headers. Hope this helps.

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 = ThisWorkbook
Set sht1 = wkbk.Worksheets("Sheet1")
Set sht2 = wkbk.Worksheets("Sheet2")

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
Hi,

Thank you for looking into this but it fails. It states "Method 'Range' of object'_Worksheet' failed
 
Upvote 0
Have you replaced "Sheet1" and "Sheet2" with your sheet names?
 
Upvote 0
that's where it fails
 

Attachments

  • Capture.JPG
    Capture.JPG
    38.8 KB · Views: 7
Upvote 0
What is the value of X when you hit that line?
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top