Compare 2 worksheets in Excel and delete rows not contained in both.

[Shelley]

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.


Sub CompareWorkshee ts()
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("Cur rentYear")
Set wsPreviousYear = Worksheets("Pre viousYear")

'---- Current Year Worksheet
With wsCurrentYear
wsCurrentYearLa stRow = .Cells(Rows.Cou nt, 1).End(xlUp).Ro w
Set rngCurrent = .Range("a2:a" & wsCurrentYearLa stRow)
End With

'---- Previous Year Worksheet
With wsPreviousYear
wsPreviousYearL astRow = .Cells(Rows.Cou nt, 1).End(xlUp).Ro w
Set rngPrevious = .Range("a8:a" & wsPreviousYearL astRow)
Set rngDelete = .Range("q8:q" & wsPreviousYearL astRow)
End With


'---- 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
wsPreviousYearN extRow = wsPreviousYearL astRow + 1
For Each cell In rngCurrent
res = Application.Mat ch(cell, rngPrevious, 0)
If IsError(res) Then ' Add to end of Previous Year
wsPreviousYear. Cells(wsPreviou sYearNextRow, "A") = cell.Value
wsPreviousYear. Cells(wsPreviou sYearNextRow, "R") = "Added"
wsPreviousYearN extRow = wsPreviousYearN extRow + 1
End If
Next cell


'---- 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.Mat ch(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
End If
Next cell

'---- 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
With rngDelete
Set c = .Find("Delete", LookIn:=xlValue s)
If Not c Is Nothing Then
firstAddress = c.Address
Do
'---- 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
End If
End With

End Sub

I REALLY NEED YOUR HELP ASAP.

THANKS.

[Killer42]

I don't recall how to delete a row either, just at the moment. But a good way to check these sort of things is to start recording a new macro, do the operation yourself, then grab the code which was generated.

[Killer42]

Sorry, I didn't realise you had more comments about the problem in your code. I haven't had a chance to read the whole thing yet (maybe at lunch time).

One question, though. When you refer to "deleting" a row, do you mean to literally delete the row, or simply blank out the data on that row?

[Shelley]

Yes, I mean to literally delete a row.

I did finally get things to work, partially.

I replaced the following line of code:
wsPreviousYear. Cells(cell.Row, "Q") = "Delete"
With:
wsPreviousYear. Rows(cell.Row). Delete

This did actually delete the rows, but there is a problem.

If the rows are in consecutive rows, it skips every other row.

Example: If it should delete rows 21, 25, 38, 45, 50, 51, 52, 53, 54, 55 & 56
It deletes rows 21, 25, 38, 45, 50, 52, 54, & 56, but does NOT delete rows 51, 53 and 55.

It works fine when I add the word "Delete" to row Q, but does not work fine when actually deleting the row. I thought it had something to do with it actually deleting the row, that it moved down a row and was not looking in that particular row, so I added the following code, but that didn't make a difference either.

For Each cell In rngPrevious
res = Application.Mat ch(cell, rngCurrent, 0)
If IsError(res) Then
Dim ReturnPreviousR ow
ReturnPreviousR ow = ("A" & cell.Row - 1)
'---- DELETE THE ENTIRE ROW
wsPreviousYear. Rows(cell.Row). Delete

Range(ReturnPre viousRow).Selec t
End If
Next Cell

Please help.

[Killer42]

Hi.

I apologise as I'm in a rush, and once again don't have time to look into your problem in detail. However, the "every second row" thing probably means you are deleting a row, then moving down to the "next" one. Keep in mind that deleting a row will be moving everything up, so the "next" row is now at the spot you just processed. In this case you need to effectively jump "up" a row, or at least somehow skip the move down.

[Killer42]

Sorry again, I did find a minute to read further, and I see you've tried to deal with the lost row. I'm not that familiar with addressing of rows, cells etc in Excel, but I would suspect that the ReturnPreviousR ow construction might be failing to do what you hope. For one thing, it'll depend on what "previous" means. If it means the row you were just in, that doesn't exist any more.

[Shelley]

Yep. That's the problem.

Once a row gets deleted, the next row down becomes the current row, so when the code deletes a row, the next row becomes the current row, but the code may think it has already deleted that row, so it moves on, thus skipping that row that used to be the next row. After I delete a row, it needs to check the same row again in case the next row down has moved up and has become the current row, but I don't know how to get it to do this. As you can see, I tried moving back a row first and then forward again, but that did not work.

Any ideas?

[Killer42]

Yep. That's the problem.

Once a row gets deleted, the next row down becomes the current row, so when the code deletes a row, the next row becomes the current row, but the code may think it has already deleted that row, so it moves on, thus skipping that row that used to be the next row. After I delete a row, it needs to check the same row again in case the next row down has moved up and has become the current row, but I don't know how to get it to do this. As you can see, I tried moving back a row first and then forward again, but that did not work.

Sorry, in a real rush here again, but could you try just moving back a row and continuing? In other words, does it matter if you process a row twice? Maybe you could even jump back and start from the beginning each time. Inefficient perhaps, but hey, if it works...

[Shelley]

Finally got it.

Here's the code for anyone want to do the same thing.

Sub CompareWorkshee ts()
Dim wbCurrentYear As Workbook, wbPreviousYear As Workbook
Dim wsCurrentYear As Worksheet, wsPreviousYear As Worksheet
Dim rngCurrent As Range, rngPrevious As Range, rngDelete As Range
Dim res As Variant, cell As Range, c, firstAddress
Dim wsCurrentYearLa stRow As Long, wsPreviousYearL astRow As Long
Dim wsPreviousYearN extRow As Long

Set wbCurrentYear = Workbooks("New" )
Set wbPreviousYear = Workbooks("Old" )
Set wsCurrentYear = Worksheets("Cur rentYear")
Set wsPreviousYear = Worksheets("Pre viousYear")

'---- Current Year Worksheet
With wsCurrentYear
wsCurrentYearLa stRow = .Cells(Rows.Cou nt, 1).End(xlUp).Ro w
Set rngCurrent = .Range("a2:a" & wsCurrentYearLa stRow)
End With

'---- Previous Year Worksheet
With wsPreviousYear
wsPreviousYearL astRow = .Cells(Rows.Cou nt, 1).End(xlUp).Ro w
Set rngPrevious = .Range("a8:a" & wsPreviousYearL astRow)
Set rngDelete = .Range("q8:q" & wsPreviousYearL astRow)
End With


'---- 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
wsPreviousYearN extRow = wsPreviousYearL astRow + 1
For Each cell In rngCurrent
res = Application.Mat ch(cell, rngPrevious, 0)
If IsError(res) Then ' Add to end of Previous Year
wsPreviousYear. Cells(wsPreviou sYearNextRow, "A") = cell.Value
' wsPreviousYear. Cells(wsPreviou sYearNextRow, "R") = "Added"
wsPreviousYearN extRow = wsPreviousYearN extRow + 1
End If
Next cell


'---- 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 x = rngPrevious.Cou nt To 1 Step -1
Set cell = rngPrevious(x)
res = Application.Mat ch(cell, rngCurrent, 0)
If IsError(res) Then
'---- IF PERSON EXISTS IN PREVIOUS YEAR AND NOT IN CURRENT YEAR,
'---- DELETE THE ENTIRE ROW
wsPreviousYear. Rows(cell.Row). Delete
End If
Next x

End Sub

[Killer42]

I'm just re-posting Shelley's message with [c o d e] tags for readability, for future reference. It maintains your indenting, and so on.

Glad to hear you got it working, Shelley. :)

Finally got it.

Here's the code for anyone want to do the same thing.

Sub CompareWorksheets()
Dim wbCurrentYear As Workbook, wbPreviousYear As Workbook
Dim wsCurrentYear As Worksheet, wsPreviousYear As Worksheet
Dim rngCurrent As Range, rngPrevious As Range, rngDelete As Range
Dim res As Variant, cell As Range, c, firstAddress
Dim wsCurrentYearLastRow As Long, wsPreviousYearLastRow As Long
Dim wsPreviousYearNextRow As Long
 
Set wbCurrentYear = Workbooks("New")
Set wbPreviousYear = Workbooks("Old")
Set wsCurrentYear = Worksheets("CurrentYear")
Set wsPreviousYear = Worksheets("PreviousYear")
 
'---- Current Year Worksheet
With wsCurrentYear
    wsCurrentYearLastRow = .Cells(Rows.Count, 1).End(xlUp).Row
    Set rngCurrent = .Range("a2:a" & wsCurrentYearLastRow)
End With
 
'---- Previous Year Worksheet
With wsPreviousYear
    wsPreviousYearLastRow = .Cells(Rows.Count, 1).End(xlUp).Row
    Set rngPrevious = .Range("a8:a" & wsPreviousYearLastRow)
    Set rngDelete = .Range("q8:q" & wsPreviousYearLastRow)
End With
 
 
'---- 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
    End If
Next cell
 
 
'---- 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 x = rngPrevious.Count To 1 Step -1
    Set cell = rngPrevious(x)
    res = Application.Match(cell, rngCurrent, 0)
    If IsError(res) Then
        '---- IF PERSON EXISTS IN PREVIOUS YEAR AND NOT IN CURRENT YEAR,
        '---- DELETE THE ENTIRE ROW
        wsPreviousYear.Rows(cell.Row).Delete
    End If
Next x
 
End Sub