比较Excel中的2个工作表并删除两者中不包含的行。 [英] Compare 2 worksheets in Excel and delete rows not contained in both.

问题描述

将当前年度工作表与上一年工作表进行比较，以及当前年度工作表中是否存在SSN&不在上一年 - 从当前年度工作表中复制此行&粘贴到上一年工作表


将上一年工作表与当前年度工作表进行比较，以及上一年工作表中是否存在SSN&不在当前年度工作表中 - 从上一年工作表中删除此行 - 这就是我遇到的麻烦。



这是我到目前为止的代码。我只是不知道如何删除该行。


Sub CompareWorksheets（）

Dim wsCurrentYear As Worksheet，wsPreviousYear As Worksheet

Dim rngCurrent As Range，rngPrevious作为范围

Dim LastRow As Long

Dim res As Variant


Set wsCurrentYear = Worksheets（" CurrentYear"）

设置wsPreviousYear =工作表（" PreviousYear"）

''----当前年份工作表

wsCurrentYear
wsCurrentYearLastRow = .Cells（Rows.Count，1）.End（xlUp）.Row

设置rngCurrent = .Range（" a2：a"& wsCurrentYearLastRow）

结束


''----上一年工作表

wsPreviousYear

wsPreviousYearLastRow = .Cells （Rows.Count，1）.End（xlUp）.Row

设置rngPrevious = .Range（" a8：a"& wsPreviousYearLastRow）

设置rngDelete =。范围（q8：q& wsPreviousYearLastRow）

结束



''----将当前年份工作表与上一年工作表进行比较

''----如果当前年度工作表中存在SSN&不是在上一年 -

''----从本年度工作表中复制此行&粘贴到上一年工作表

wsPreviousYearNextRow = wsPreviousYearLastRow + 1

每个单元格在rngCurrent

res = Application.Match（cell，rngPrevious，0 ）

如果IsError（res）那么''添加到上一年结束

wsPreviousYear.Cells（wsPreviousYearNextRow，" A"）= cell.Value

wsPreviousYear.Cells（wsPreviousYearNextRow，" R"）="已添加"

wsPreviousYearNextRow = wsPreviousYearNextRow + 1

结束如果

下一个单元格


''----比较上一年工作表和当前年份工作表

''----如果存在SSN在上一年工作表&不在当前年度工作表中 -

''----删除上一年工作表中的这一行

每个单元格在rngPrevious

res = Application.Match（cell，rngCurrent，0）

如果IsError（res）那么

''----如果在上一年而不是在当前年份，请插入这个词

''----" DELETE"在列Q所以行可以删除

wsPreviousYear.Cells（cell.Row，" Q"）="删除"

''----我真的会在这里删除这里的行，而不是添加

''---- WORD" DELETE"到行和& COLUMN Q

结束如果

下一个单元格


''----由于我不知道如何获取它要删除上面的行，我试过了。

''----删除那些已删除字段的行Q

使用rngDelete

设置c = .Find（" Delete"，LookIn：= xlValues）

如果不是c则什么都没有

firstAddress = c.Address

请

''----更改删除字样到删除行只是为了看看这是否正常工作！


''----如果我尝试删除行（取代改变价值删除行） >
''----然后它就开始了 - 设置c = FindNext（c）


c.Value =" Delete Row"

设置c = .FindNext（c）

循环而不是c是什么并且c.Address<> firstAddress

结束如果

结束


结束次级


我真的需要你的帮助。


谢谢。

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.

推荐答案

我也不记得如何删除一行，就在此刻。但检查这些事情的好方法是开始录制一个新的宏，自己做操作，然后抓取生成的代码。

I don''t recall how to delete a row either, just at the moment. But a good way to check these sort of things is to start recording a new macro, do the operation yourself, then grab the code which was generated.

对不起，我没有意识到你的代码中有关于这个问题的更多评论。我没有''有机会读完整件事（也许是在路上nch time）。


但是有一个问题。当您提到删除时一行，你的意思是字面上删除该行，或者只是删除该行上的数据？

Sorry, I didn''t realise you had more comments about the problem in your code. I haven''t had a chance to read the whole thing yet (maybe at lunch time).

One question, though. When you refer to "deleting" a row, do you mean to literally delete the row, or simply blank out the data on that row?

是的，我的意思是删除一行。


我终于让事情变得有效了。


我替换了以下代码行：

wsPreviousYear.Cells（cell.Row，" Q"）="删除"

随附：

wsPreviousYear.Rows（cell.Row）。删除


这确实删除了行，但是有问题。


如果行是连续的行，它会跳过每隔一行。


示例：如果它应该删除行21,25,38,45,50,51,52,53,54,55& 56

删除21,25,38,45,50,52,54行和& 56，但不删除第51,53和55行。


当我添加单词删除时，它工作正常。行Q，但在实际删除行时不能正常工作。我认为这与它实际上删除行有关，它向下移动了一行并且没有查看那个特定的行，所以我添加了以下代码，但这也没有什么区别。


每个单元格在rngPrevious

res = Application.Match（cell，rngCurrent，0）

If IsError（res）然后

Dim ReturnPreviousRow

ReturnPreviousRow =（" A"& cell.Row - 1）

''----删除整个行

wsPreviousYear.Rows（cell.Row）。删除


范围（ReturnPreviousRow）。选择

结束如果

下一个单元格


请帮忙。

Yes, I mean to literally delete a row.

I did finally get things to work, partially.

I replaced the following line of code:
wsPreviousYear.Cells(cell.Row, "Q") = "Delete"
With:
wsPreviousYear.Rows(cell.Row).Delete

This did actually delete the rows, but there is a problem.

If the rows are in consecutive rows, it skips every other row.

Example: If it should delete rows 21, 25, 38, 45, 50, 51, 52, 53, 54, 55 & 56
It deletes rows 21, 25, 38, 45, 50, 52, 54, & 56, but does NOT delete rows 51, 53 and 55.

It works fine when I add the word "Delete" to row Q, but does not work fine when actually deleting the row. I thought it had something to do with it actually deleting the row, that it moved down a row and was not looking in that particular row, so I added the following code, but that didn''t make a difference either.

For Each cell In rngPrevious
res = Application.Match(cell, rngCurrent, 0)
If IsError(res) Then
Dim ReturnPreviousRow
ReturnPreviousRow = ("A" & cell.Row - 1)
''---- DELETE THE ENTIRE ROW
wsPreviousYear.Rows(cell.Row).Delete

Range(ReturnPreviousRow).Select
End If
Next Cell

Please help.

这篇关于比较Excel中的2个工作表并删除两者中不包含的行。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！