删除大表中的行

Question

我有一个大表，我想从其中删除与A列的特定值相对应的整行。我使用for循环，但我正在寻找一种更有效的方法。

Sub deleteRow()
    For i = 1 To 900000
        If Cells(i, 1) > 7 Then
            Rows(i).Select
            Selection.Delete
        End If
    Next i
End Sub

Answer 1

这个简单的宏会为你工作：

不需要循环

对于表，请使用以下内容：

Sub deleteRow()

    With ActiveSheet.ListObjects("Table1") ' Change table name

        .Range.AutoFilter Field:=1, Criteria1:=">7"
        .DataBodyRange.Delete

    End With

End Sub

它还将显示一个警告，当它出现时选择Yes

对于简单的范围对象，请使用以下命令：

Sub deleteRow()

    With ActiveSheet.UsedRange

        .AutoFilter Field:=1, Criteria1:=">7"
        .Offset(1, 0).SpecialCells(xlCellTypeVisible).Delete xlShiftUp

    End With

End Sub

使用各种表格操作命令的有用链接。这里

Answer 2

您应该在结束时立即删除它们，而不是在每个实例上删除。

尝尝这个。

Sub deleteRow()
Dim killRNG As Range
Set killRNG = Cells(Rows.Count, 1).EntireRow

For i = 1 To 900000


If Cells(i, 1) > 7 Then

Set killRNG = Union(Cells(i, 1).EntireRow, killRNG)

End If
Next i

killRNG.Delete

End Sub

Answer 3

一些窍门：

Option Explicit

Sub deleteRow()

    Dim i As Long, LastRow As Long

    'Create a with statement refer to the sheet where data are store
    With ThisWorkbook.Worksheets("Sheet1")

        'Find LastRow of column A. There is no need to loop up to 900000
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

        'When you loop aiming to delete you loop from bottom to top
        For i = LastRow To 1 Step -1

            'In both ".Cells(i, 1) > 7" & ".Rows(i).EntireRow.Delete" we use "." before
            If .Cells(i, 1).Value > 7 Then
                .Rows(i).EntireRow.Delete
            End If

        Next i

    End With

End Sub