Compare 2 worksheets and delete rows not contained in both.
Posted on 2006-11-30
Compare Current Year Worksheet with Previous Year Worksheet and if SSN exists in Current Year Worksheet & Not in Previous Year - Copy this Row from Current Year Worksheet & Paste into Previous Year Worksheet
Compare Previous Year Worksheet with Current Year Worksheet and if SSN exists in Previous Year Worksheet & Not in Current Year Worksheet - Delete this Row out of Previous Year Worksheet - THIS IS WHERE I'M HAVING TROUBLE.
This is the code I have so far. I just don't know how to delete the row.
Dim wsCurrentYear As Worksheet, wsPreviousYear As Worksheet
Dim rngCurrent As Range, rngPrevious As Range
Dim LastRow As Long
Dim res As Variant
Set wsCurrentYear = Worksheets("CurrentYear")
Set wsPreviousYear = Worksheets("PreviousYear")
'---- Current Year Worksheet
wsCurrentYearLastRow = .Cells(Rows.Count, 1).End(xlUp).Row
Set rngCurrent = .Range("a2:a" & wsCurrentYearLastRow)
'---- Previous Year Worksheet
wsPreviousYearLastRow = .Cells(Rows.Count, 1).End(xlUp).Row
Set rngPrevious = .Range("a8:a" & wsPreviousYearLastRow)
Set rngDelete = .Range("q8:q" & wsPreviousYearLastRow)
'---- Compare Current Year Worksheet with Previous Year Worksheet
'---- If SSN exists in Current Year Worksheet & Not in Previous Year -
'---- Copy this Row from Current Year Worksheet & Paste into Previous Year Worksheet
wsPreviousYearNextRow = wsPreviousYearLastRow + 1
For Each cell In rngCurrent
res = Application.Match(cell, rngPrevious, 0)
If IsError(res) Then ' Add to end of Previous Year
wsPreviousYear.Cells(wsPreviousYearNextRow, "A") = cell.Value
wsPreviousYear.Cells(wsPreviousYearNextRow, "R") = "Added"
wsPreviousYearNextRow = wsPreviousYearNextRow + 1
'---- Compare Previous Year Worksheet with Current Year Worksheet
'---- If SSN exists in Previous Year Worksheet & Not in Current Year Worksheet -
'---- Delete this Row out of Previous Year Worksheet
For Each cell In rngPrevious
res = Application.Match(cell, rngCurrent, 0)
If IsError(res) Then
'---- IF IN PREVIOUS YEAR AND NOT IN CURRENT YEAR, INSERT THE WORD
'---- "DELETE" IN COLUMN Q SO THAT ROW CAN BE DELETED
wsPreviousYear.Cells(cell.Row, "Q") = "Delete"
'---- I WOULD REALLY RATHER DELETE THE ROW HERE IN LIEU OF ADDING THE
'---- WORD "DELETE" TO THE ROW & COLUMN Q
'---- SINCE I DIDN'T KNOW HOW TO GET IT TO DELETE THE ROW ABOVE, I TRIED
'---- DELETING THE ROWS THAT HAVE THE WORD DELETE ADDED TO COLUMN Q
Set c = .Find("Delete", LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
'---- Change the word "Delete" to "Delete Row" just to see if this is working properly!
'---- IF I TRY TO DELETE THE ROWS (IN LIEU OF CHANGE THE VALUE TO "DELETE ROW"
'---- THEN IT BOMBS ON - Set c = FindNext(c)
c.Value = "Delete Row"
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
I REALLY NEED YOUR HELP ASAP.