excel - 逐行，如果单元格 H= 单元格 I，则删除整行

Question

如果单元格 H1 = 单元格 I1，则删除整行。然后下一行，单元格 H2 <> 单元格 I2，保留。然后是下一行。

如果行的两个单元格相同，我想删除整行。我进行了搜索，这是最接近我想要实现并修改代码的。

Sub CellAequalCellB ()
    Dim ws As Worksheet
    Dim rng As Range
    Dim lastRow As Long

    Set ws = ThisWorkbook.Sheets("Sheet1")

    lastRow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row

    Set rng = ws.Range("H1:I" & lastRow)

    ' filter and delete all but header row
    With rng
        .AutoFilter field:=1, Criteria1:=" .AutoFilter field:=2 "

SpecialCells(xlCellTypeVisible).EntireRow.Delete
    End With

    ' turn off the filters
    ws.AutoFilterMode = False
End Sub

但是，当我运行时， wholerow.delete 有错误。

标签： excelvba

Answer 1

您的 AutoFilter 代码有很多错误，因此我将方法更改为简单的向后循环。

sub CellAequalCellB ()
    Dim ws As Worksheet, rng As Range
    Dim lastRow As Long, i as long

    with ThisWorkbook.Sheets("Sheet1")

        lastRow = .Range("A" & .Rows.Count).End(xlUp).Row

        for i=lastrow to 1 step -1
            if .cells(i, "H") = .cells(i, "I") then _
                .rows(i).EntireRow.Delete
        next i

    end with

End Sub