Solved

Compare 2 worksheets and delete rows not contained in both.

Posted on 2006-11-30
12
164 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
ID: 18051040
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
ID: 18056125
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
ID: 18056240
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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 3

Expert Comment

by:Obadah_HighTech
ID: 18057946
Hello,

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

Expert Comment

by:Obadah_HighTech
ID: 18058027
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
ID: 18058282
How do I upload a file with this question?
0
 
LVL 3

Expert Comment

by:Obadah_HighTech
ID: 18058770
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
ID: 18059796
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
ID: 18060174
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
ID: 18070819
I uploaded my file so you could take a look at it.  

Thanks.
0
 

Author Comment

by:ShelleyNeuerburg
ID: 18070848
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
ID: 18073409
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

ScreenConnect 6.0 Free Trial

Want empowering updates? You're in the right place! Discover new features in ScreenConnect 6.0, based on partner feedback, to keep you business operating smoothly and optimally (the way it should be). Explore all of the extras and enhancements for yourself!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

809 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