Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Compare 2 worksheets and delete rows not contained in both.

Posted on 2006-11-30
12
Medium Priority
?
171 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 2000 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
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…
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…

824 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