Conditionally deleting data between two  workbooks

Sachin Singh
Sachin Singh used Ask the Experts™
on
I have two files 1)book1        2)book2
my both files located in C:\Users\user\Desktop
now what i want book2 has additional data so what i want  see all the symbols of sheet1 of  book1 and delete the additional symbols (entire row) of sheet1 book2 and  keep all the data
plz see the sample file
I will place the code in Book2
we cant copy and paste the data bcoz except symbol rest data will be different
Book1.xlsb
Book2.xlsb
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
So what is it exactly what you want?
1. Search for the 'Symbol' of Book1 in Book2.
2. If not found in book2, delete entire row.
 is it??

Author

Commented:
Yes sumit sir
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
Please give this a try...
The following code assumes that Book1.xlsb and Book2.xlsb are saved in the same folder.

Sub DeleteRowsFromBook2()
Dim Book1 As Workbook, Book2 As Workbook
Dim ws1 As Worksheet, ws2 As Worksheet
Dim Book1Name As String, Book1Path As String
Dim x1, x2, dict
Dim Rng As Range
Dim i As Long
Application.ScreenUpdating = False
Set Book2 = ThisWorkbook
Set ws2 = Book2.Worksheets("Sheet1")

Book1Path = Book2.Path & "\"
Book1Name = "Book1.xlsb"

Set Book1 = Workbooks.Open(Book1Path & Book1Name)
Set ws1 = Book1.Worksheets("Sheet1")

x1 = ws1.Range("A1").CurrentRegion.Value
x2 = ws2.Range("A1").CurrentRegion.Value
Set dict = CreateObject("Scripting.Dictionary")
Book1.Close False

For i = 2 To UBound(x1, 1)
    dict.Item(x1(i, 2)) = ""
Next i

For i = 2 To UBound(x2, 1)
    If Not dict.exists(x2(i, 2)) Then
        If Rng Is Nothing Then
            Set Rng = ws2.Cells(i, 2)
        Else
            Set Rng = Union(Rng, ws2.Cells(i, 2))
        End If
    End If
Next i
If Not Rng Is Nothing Then Rng.EntireRow.Delete
Application.ScreenUpdating = True
End Sub

Open in new window

Click the button called "Delete Rows" on Sheet1 to run the code.
Book2.xlsb

Author

Commented:
Thnx Neeraj Sir  and sumit Sir for ur great  support
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
You're welcome Sachin!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial