删除 vba excel 中的行，但仅删除备用行

Question

我正在尝试使用 vba 从 excel 中删除重复的行。该函数检查列中的值，如果找到该值，则删除该行。我编写了以下代码，它可以工作，但备用行被删除。一个被删除一个不被删除。在每次迭代中都可以找到匹配项。我在任何地方都错了吗？

Private Function DeletePreviousRecords(Program As String, targetSheet As String) As Boolean
Dim lastRow As Long, iCTR As Integer: iCTR = 2
Dim IsDone As Boolean
lastRow = GetLastRow(targetSheet)
Do
   If Sheets(targetSheet).range("G" & CStr(iCTR)) = Program Then
    MsgBox "Found Match at Row -" & CStr(iCTR)
    Sheets(targetSheet).Rows(iCTR).Delete
    IsDone = True
   End If
   iCTR = iCTR + 1
Loop While iCTR < lastRow
DeletePreviousRecords = IsDone
End Function
Private Function GetLastRow(sheetName As String) As Long
Dim lastRow As Long
'ActiveWorkbook.Sheets(targetSheet).Cells(Sheets(targetSheet).Rows.Count, "C").End(xlUp).Row
With ActiveWorkbook.Sheets(sheetName)
    lastRow = .Cells(Sheets(sheetName).Rows.Count, "C").End(xlUp).Row
End With
GetLastRow = lastRow + 1
End Function

Answer 1

删除行时，您必须意识到您正在删除正在循环访问的相同行。我的建议是从最后一行开始并使用 -1 循环计数器进行迭代。这样，您可以安全地删除一行，而不会影响剩余的行。换句话说 - 从 lastRow 向后迭代到 2(或任何你的起始行(。