Compare 2 worksheets and delete rows not contained in both.

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 CompareWorksheets()
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
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 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
    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:=xlValues)
    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.
ShelleyNeuerburgAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Obadah_HighTechConnect With a Mentor Commented:
Hello,

sorry for that logical error, I fixed your file and uploaded it

To download the file, you must be logged into EE-Stuff. Here are two pages that will display your file, if logged in:

View all files for Question ID: 22078645
https://filedb.experts-exchange.com/incoming/ee-stuff/1601-DeleteRows.ZIPhttps://filedb.experts-exchange.com/incoming/ee-stuff/1607-mytestupload_mod.zip
https://filedb.experts-exchange.com/incoming/ee-stuff/1577-compair.zip


Direct link to your file
https://filedb.experts-exchange.com/incoming/ee-stuff/1607-mytestupload_mod.zip 
0
 
Obadah_HighTechCommented:
Hello,

I uploaded a sample file with the code.

To download the file, you must be logged into EE-Stuff. Here are two pages that will display your file, if logged in:

View all files for Question ID: 22078645
https://filedb.experts-exchange.com/incoming/ee-stuff/1601-DeleteRows.ZIPhttps://filedb.experts-exchange.com/incoming/ee-stuff/1607-mytestupload_mod.zip
https://filedb.experts-exchange.com/incoming/ee-stuff/1577-compair.zip


Direct link to your file
https://filedb.experts-exchange.com/incoming/ee-stuff/1577-compair.zip 
0
 
ShelleyNeuerburgAuthor Commented:
This partially works.  It does not delete the last row that it should.  On your example it should add 5, 8 and 9 to the previous year worksheet (which it does) and it should delete 10 & 11 on the previous year worksheet, but it only deletes 10.  On my worksheets, it deletes all but the very last one.  
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
ShelleyNeuerburgAuthor Commented:
Upon further testing, it doesn't delete all but the last one, if the rows that need to be deleted are in consecutive rows, it skips every other one.  

Example:  

If it should delete rows 5, 15, 25, 26, 27, 28, 29, and 35,
it deletes 5, 15, 25, 27, 29 and 35.  It skips rows 26 and 28.  

0
 
Obadah_HighTechCommented:
Hello,

I just modified your code
I will put mine when I write it (hopefully soon)
0
 
Obadah_HighTechCommented:
Hello,

I re-examined the code, it looks OK, if you have a problem in some file PLEASE upload the file to this question
0
 
ShelleyNeuerburgAuthor Commented:
How do I upload a file with this question?
0
 
Obadah_HighTechCommented:
it is easy, just goto

http://www.ee-stuff.com/Expert

and login with the same account as this site
then click upload new file
0
 
ahammarCommented:
Just a thought with why it only deletes every other consecutive row...

Once a row gets deleted, the next row down becomes the current row, so when your 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.  In other words... after you delete a row, you need to check the same row again in case the next row down has moved up and has become the current row.

I did not study the code, but I have had this problem before, so it's just a thought in case it helps...

Cheers!
ahammar

0
 
Obadah_HighTechCommented:
Hello,

Here is the code I used to delete the rows, I used "for each" and then cell.row :

'---- 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
        'Just Delete the row NOW
        wsPreviousYear.Rows(cell.Row).Delete
    End If
Next cell
0
 
ShelleyNeuerburgAuthor Commented:
I uploaded my file so you could take a look at it.  

Thanks.
0
 
ShelleyNeuerburgAuthor Commented:
Here is the file.  It's doing exactly what ahammar explains, but I don't know how to write the code to take care of this problem.  After deleting the row, I tried selecting the previous row and then going forward again, but this did not work.  I can run the same code several times to take care of the problem, but I wouldn't know exactly how many times to run it, so I would like to fix it correctly.  

Thanks.  

View all files for Question ID: 22078645
https://filedb.experts-exchange.com/incoming/ee-stuff/1601-DeleteRows.ZIPhttps://filedb.experts-exchange.com/incoming/ee-stuff/1607-mytestupload_mod.zip
https://filedb.experts-exchange.com/incoming/ee-stuff/1577-compair.zip

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.