vba - 根据条件删除行

Question

我有一些代码，所以我可以将特定的行移动到特定的工作表，其结构如下:

表 1(包含所有数据)

工作表 2(要移动的行的目标工作表)

因此，基本上代码会在特定列上查找关键字，并将指定列上符合该条件的所有行从表 1 复制到表 2，这就像一个魅力。我遇到的问题是因为数据组织，我需要在复制行后删除它们，我尝试使用 .cut target而不是 .copy target ，它也可以工作，但它需要很长时间(大约 1+ 分钟)，而且看起来整个时间都被卡住了，因为它不允许你选择任何东西。

有什么建议可以更有效地完成此任务吗？我正在学习VBA，所以请多多包涵。

Sub Copydatatoothersheet()
    Dim c As Range
    Dim j As Integer
    Dim Source As Worksheet
    Dim Target As Worksheet

    ' Change worksheet designations as needed
    Set Source = ActiveWorkbook.Worksheets("Sheet1")
    Set Target = ActiveWorkbook.Worksheets("Sheet2")

    j = 3     ' Start copying to row 3 in target sheet
    Application.ScreenUpdating = False
    For Each c In Source.Range("BB:BB")
        If c = "UNPAID" Then
        'THIS IS THE LINE WHERE I REPLACE COPY WITH CUT
           Source.Rows(c.Row).Copy Target.Rows(j)
           j = j + 1
        End If
    Next c
    Application.ScreenUpdating = True
End Sub

Answer 1

尝试将所需范围存储在变量中，然后删除该存储范围的整行

Sub Copydatatoothersheet()
Dim c As Range
Dim j As Integer
Dim source As Worksheet
Dim target As Worksheet
Dim oRange As Range

' Change worksheet designations as needed
Set source = ActiveWorkbook.Worksheets("Sheet1")
Set target = ActiveWorkbook.Worksheets("Sheet2")

j = 3     ' Start copying to row 3 in target sheet
Application.ScreenUpdating = False
For Each c In source.Range("BB:BB")
    If c = "UNPAID" Then
    'THIS IS THE LINE WHERE I REPLACE COPY WITH CUT
       source.Rows(c.Row).Copy target.Rows(j)
       If oRange Is Nothing Then Set oRange = c Else Set oRange = 
Union(oRange, c)
       j = j + 1
    End If
Next c
If Not oRange Is Nothing Then oRange.EntireRow.Delete
Application.ScreenUpdating = True
End Sub