Solved

Compare 2 worksheets and delete rows not contained in both.

Posted on 2006-11-30
12
162 Views
Last Modified: 2016-08-29
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.
0
Comment
Question by:ShelleyNeuerburg
  • 6
  • 5
12 Comments
 
LVL 3

Expert Comment

by:Obadah_HighTech
Comment Utility
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
 

Author Comment

by:ShelleyNeuerburg
Comment Utility
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
 

Author Comment

by:ShelleyNeuerburg
Comment Utility
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
 
LVL 3

Expert Comment

by:Obadah_HighTech
Comment Utility
Hello,

I just modified your code
I will put mine when I write it (hopefully soon)
0
 
LVL 3

Expert Comment

by:Obadah_HighTech
Comment Utility
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
 

Author Comment

by:ShelleyNeuerburg
Comment Utility
How do I upload a file with this question?
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 3

Expert Comment

by:Obadah_HighTech
Comment Utility
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
 
LVL 23

Expert Comment

by:ahammar
Comment Utility
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
 
LVL 3

Expert Comment

by:Obadah_HighTech
Comment Utility
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
 

Author Comment

by:ShelleyNeuerburg
Comment Utility
I uploaded my file so you could take a look at it.  

Thanks.
0
 

Author Comment

by:ShelleyNeuerburg
Comment Utility
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
 
LVL 3

Accepted Solution

by:
Obadah_HighTech earned 500 total points
Comment Utility
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

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now