Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

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

Posted on 2006-11-30
Medium Priority
169 Views
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
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
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)
End If
End With

End Sub

I REALLY NEED YOUR HELP ASAP.

THANKS.
0
Question by:ShelleyNeuerburg
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 6
• 5

LVL 3

Expert Comment

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/1577-compair.zip

https://filedb.experts-exchange.com/incoming/ee-stuff/1577-compair.zip
0

Author Comment

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

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

LVL 3

Expert Comment

ID: 18057946
Hello,

I will put mine when I write it (hopefully soon)
0

LVL 3

Expert Comment

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

ID: 18058282
How do I upload a file with this question?
0

LVL 3

Expert Comment

ID: 18058770
it is easy, just goto

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

and login with the same account as this site
0

LVL 23

Expert Comment

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

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

ID: 18070819
I uploaded my file so you could take a look at it.

Thanks.
0

Author Comment

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/1577-compair.zip

0

LVL 3

Accepted Solution

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/1577-compair.zip

0

## Featured Post

Question has a verified solution.

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

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asseâ€¦
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most pâ€¦
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â€¦
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applicâ€¦
###### Suggested Courses
Course of the Month10 days, 6 hours left to enroll