vba - Excel VBA在一行循环中删除三重重复

Question

当 G、H、I 列中的单元格中的所有 3 个数值都相等时，我想删除整行。我写了一个 vba 代码，它不会删除任何内容。有人可以建议吗？

Sub remove_dup()

Dim rng As Range
Dim NumRows As Long
Dim i As Long


Set rng = Range("G2", Range("G2").End(xlDown))
NumRows = Range("G2", Range("G2").End(xlDown)).Rows.Count

For i = 2 To NumRows

Cells(i, 7).Select
If Cells(i, 7).Value = Cells(i, 8).Value = Cells(i, 9).Value Then
EntireRow.Delete
Else
Selection.Offset(1, 0).Select
End If

Next i

End Sub

Answer 1

试试这个代码。删除行时，始终从最后一行开始，朝着第一行工作。这样你就确定你不会跳过任何行。

Sub remove_dup()
Dim rng As Range
Dim NumRows As Long
Dim i As Long

NumRows = Range("G2", Range("G2").End(xlDown)).Rows.Count
For i = NumRows + 1 To 2 Step -1
        If Cells(i, 7).Value = Cells(i, 8).Value And Cells(i, 7).Value = Cells(i, 9).Value Then
                    Cells(i, 7).EntireRow.Delete
        Else
        End If
Next i
End Sub