Code: Select all
Sub test()
Dim wb1 As Workbook, wb2 As Workbook
Set wb1 = Workbooks.Open("C:UsersWolfieeeStyleDesktop1.xls")
Set wb2 = Workbooks.Open("C:UsersWolfieeeStyleDesktop2.xlsx")
Set sh1 = wb1.Worksheets.Item(1)
Set sh2 = wb2.Worksheets.Item(2) '---> you put item(1), while the sheet in wb2 which has data is in sheet2.
Set rngWb1 = sh1.Range("B2", sh1.Range("B" & sh1.Rows.Count).End(xlUp))
Set rngWb2 = sh2.Range("A2", sh2.Range("A" & sh2.Rows.Count).End(xlUp))
sh1.Cells(1, 10).Value = "KEEP"
With sh1
For Each c1 In rngWb2
sym = c1.Value
num = c1.Offset(0, 1).Value
For Each c2 In rngWb1
tt = c2.Value
If c2.Value = sym And c2.Offset(0, 7).Value = num Then _
c2.Offset(0, 8).Value = "keep"
Next
Next
.Range("A1").AutoFilter Field:=10, Criteria1:="="
.Range("A2", .Range("i" & .Rows.Count).End(xlUp)) _
.SpecialCells(xlCellTypeVisible).EntireRow.Delete
.Cells.AutoFilter
End With
sh1.Cells(1, 10).EntireColumn.Delete
'wb1.Save
'wb1.Close
'wb2.Close
End Sub
this macro works perfect for this:If column I of 1.xls matches with column B of sheet2 of 2.xlsx then dont do anything & if not matched then delete entire row
little modification is there:If column B of 1.xls matches with column E of sheet1 of 2.xlsx then delete entire row & if not matched don't do anything
So i am looking for the macro for this
Thnx For the Help