Comparing two spreadsheets - deleting rows in one

Closed
Report
Posts
1
Registration date
Saturday October 5, 2013
Status
Member
Last seen
October 5, 2013
-
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
-
Hello:

I have a requirement to do the following:

- Spreadsheet #1 contains several columns of product data, including SKUs. It has several thousand rows.
- Spreadsheet #2 contains one column, which is just SKUs of existing products. Also several thousand rows, but a subset of the number of rows in spreadsheet #1,
- I need to remove all rows in spreadsheet #1 in which the SKU matches a SKU found in a row in spreadsheet #2
- The result is to be a revised spreadsheet #1 that has removed (deleted) rows that would result in duplicate products being entered.

Does anyone know how to do this?

Thank you in advance!

1 reply

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
download the sample but trivial file from

http://speedy.sh/Ae8Uh/fanway-131007.xlsm

sheet 1 and sheet 2 are data. sheet1 is copied to sheet 3 to preserve configuration

try this macro "test" on this file and cofirm whether it gives what you want. if yes use or modify the macro to suit your data;.. the macro is in vbeditor and also repeated here.

Sub test()
Dim r As Range, r2 As Range, c2 As Range, x(), j As Integer, k As Integer
Worksheets("sheet1").Cells.Clear
Worksheets("sheet3").Cells.Copy Worksheets("sheet1").Range("a1")


With Worksheets("sheet1")
Set r = Range("A1").CurrentRegion
With Worksheets("sheet2")
Set r2 = Range(.Range("A2"), .Range("A2").End(xlDown))
j = .Range("a1").End(xlDown).Row - 1
ReDim x(1 To j)
For k = 1 To j
x(k) = .Range("A2").Offset(k - 1, 0)
Next k
End With
For k = 1 To j
r.AutoFilter field:=.Range("A1").Column, Criteria1:=x(k)
r.Offset(1, 0).Resize(r.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Delete

Next k
.AutoFilterMode = False
End With
End Sub


Sub undo()
Worksheets("sheet1").Cells.Clear
Worksheets("sheet3").Cells.Copy Worksheets("sheet1").Range("a1")

End Sub