VBA代码 - 根据匹配条件将单元格中的单元格复制到Excel中的另一个工作表

我在Excel中有两张Sheet1和Sheet2 .Sheet 1有C1,C2,C3,C4,C5列 . 表2有C1,C2,C3列 . 现在我必须执行3次操作 .

1.)删除Sheet1中的所有行,其中在Sheet2的第1列中找不到第1列的值 .

2.)将表2的C2,C3的值替换为表1的C2,C3(C4,C5保持不变),其中表1的C1的值与Sheet2的C1匹配 .

3.)将表2的C1,C2,C3数据附加到表1中,其中表1中C1的值未在Sheet1的C1中找到(C4,C5将为空白) .

我能够为操作1编写VB代码 . 请帮我操作操作2和操作3 .

Sub delete_selected_rows()

Dim rng1 As Range, rng2 As Range, rngToDel As Range, c As Range
Dim lastRow As Long

With Worksheets("Sheet1")
    lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    Set rng1 = .Range("A2:A" & lastRow)
End With

Set rng2 = Worksheets("Sheet2").Range("A:A")

For Each c In rng1
    If IsError(Application.Match(c.Value, rng2, 0)) Then
        'delete incidents which are not in process
        If rngToDel Is Nothing Then
            Set rngToDel = c
        Else
            Set rngToDel = Union(rngToDel, c)
        End If
    End If
Next c

If Not rngToDel Is Nothing Then rngToDel.EntireRow.Delete

End Sub

回答(1)

2 years ago

首先,让我就第1步的解决方案发表两点评论 .

  • 如果您从底部到顶部逐行显示行,则可以删除现场的行 . (行索引仅对已删除行下的行进行更改 . )

  • IMO,最好使用 rng2.Find 而不是 Applications.Match . 范围对象的此方法返回找到匹配项的单元格,如果没有匹配项则返回 Nothing .

现在到第2步:

使用第1步的解决方案中的表示法,您可以使用 rng2.Find(c.Value).EntireRow 获取sheet2中匹配的行 . 然后,您可以使用其 Cells 属性来获取第二列和第三列 .

第3步:

您已从步骤1中了解如何找出另一个工作表中没有匹配的行 . 您只需将前三列中的值复制到sheet1的最后一行之后的行中,而不是sheet1中的匹配项 . (最好保存最后一行,然后随每个复制的行递增 . )

上面,我给出了每个步骤的简单解决方案 . 但是,如果您有非常大的表,这可能会有点慢 . 基本上,使用此解决方案,您将循环遍历行三次,每行,查询工作表中的值并搜索另一个表中的所有行以进行匹配 .

具有更好性能的方法是使用范围的 Value 属性将整个范围加载到二维数组中,或者更好 Value2 . 然后你可以做一些类似于合并连接的东西来解决你的三个问题,即你可以使用你喜欢的n * log(n)排序算法按第一列对两个数组进行排序,然后按升序逐步浏览这两个列表 . 单步执行时,可以保存要删除的行,更新相应的行,并追加其他行 . 要启用此功能,您应该在排序数组时跟踪原始行 . 最后,您将从下到上遍历标记为删除的行并删除它们 . (你不能马上删除,因为这会弄乱已删除行下面的行的行索引 . )

实现三个步骤的另一种方法是结合易用性和性能,是通过ADODB针对您的工作表编写SQL查询 .

你的第一步看起来像这样 .

DELETE [sheet1$] WHERE [sheet1$].'header of first column' NOT IN (SELECT [sheet2$].'header of first column')

第2步是更新语句,第3步是insert into语句 .