如何忽略Excel宏中基于条件删除所有行的第一行? [英] How do I ignore the first row in an Excel macro that deletes all rows based on a criteria?

问题描述

此宏旨在比较C和D列中的数据，如果C在某一行中与D不匹配，它将删除整个丝束.问题在于，因为它们不匹配，它会删除Excel工作表第1行中的标题.如何为第2到9999行而不是所有9999行运行宏.

This macro is designed to compare the data in column C and D and if C does not match D in a certain row, it deletes the entire tow. The problem is that it deletes the headers in Row 1 on the Excel sheet because they don't match. How do I run the macro for rows 2 through 9999 instead of all 9999 rows.

Sub deleteNonMatchingRows()
    Dim i As Long

    For i = 9999 To 1 Step -1 ' it will scan 9999 rows of the sheet. This number can be increased for larger sheets
        If Range("C" & i) <> Range("D" & i) Then
            Range("C" & i).EntireRow.Delete
        End If
    Next
End Sub

推荐答案

删除一行是一项耗时的操作.因此，最好一次删除所有行，并在特定范围wholeRange:

Deletion of a row is an operation that takes quite some time. Thus, it is a good idea to make all deletions at once, uniting all rows to be deleted in a specific range wholeRange:

Option Explicit

Public Sub DeleteNonMatchingRows()

    Dim LastRow As Long
    LastRow = Cells(Rows.Count, "C").End(xlUp).Row

    Dim wholeRange  As Range
    Dim iRow As Long
    For iRow = LastRow To 2 Step -1
        If Range("C" & iRow) <> Range("D" & iRow) Then
            If wholeRange Is Nothing Then
                Set wholeRange = Rows(iRow)
            Else
                Set wholeRange = Union(wholeRange, Rows(iRow))
            End If
        End If
    Next iRow

    If Not wholeRange Is Nothing Then
        wholeRange.Select       'delete this row
        Stop                    'delete this row
        wholeRange.Delete
    End If

End Sub

运行代码后，它将停止在Stop行上.您将能够看到要删除的范围.范围将被选择.一旦看到它，删除注释中提到的两行是个好主意，您将不再需要它们.

Once you run the code, it will stop on the Stop line. You will be able to see the range, which is to be deleted. The range will be selected. Once you see it, it is a good idea to delete the two rows, mentioned in the comments, you are not going to need them any more.

这篇关于如何忽略Excel宏中基于条件删除所有行的第一行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！