Conditionally deleting data between two workbooks

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
Sachin SinghAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sumit yadavCommented:
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??
0
Sachin SinghAuthor Commented:
Yes sumit sir
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Sachin SinghAuthor Commented:
Thnx Neeraj Sir  and sumit Sir for ur great  support
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome Sachin!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.