0

I am trying to delete the duplicate rows from a excel using vba. The function checks for a value in a column, if it finds it then the row is deleted. I have written the following code, it works but alternate rows get deleted. One gets deleted one does not. The match is found in every iteration. Am I wrong anywhere?

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
Nimy Alex
  • 155
  • 1
  • 3
  • 13
  • As @Jazimov said, you need to step backwards through your rows while deleting. – dwirony Apr 13 '18 at 18:58
  • 1
    I appreciate policing the site for duplicate questions, but the question cited as a duplicate does not mention trouble with a forward-loop deletion--rather, it asks *how* to delete. The answers are perhaps duplicated (in a sense) but the key point is that the *question* is not, in this case. – Jazimov Apr 13 '18 at 19:43

1 Answers1

1

When deleting rows, you have to realize that you're deleting the same rows that you're looping through. My suggestion is to start from the LAST row and iterate using a -1 loop counter. That way you can safely delete a row without affecting your remaining rows. In other words--iterate backwards from lastRow to 2 (or whatever your starting row is).

Jazimov
  • 12,626
  • 9
  • 52
  • 59
  • It worked! But why does it work in the backward iteration and not the other way around? Isn't it technically the same? – Nimy Alex Apr 13 '18 at 19:24
  • 1
    No it's not the same: Imagine you have 5 items, A, B, C, D, E. I tell you to delete the 2nd (B) and 4th (D) items. You start a loop and delete the 2nd item. Now the 4th item in the list no longer is D--it's E! That's because you've deleted B, causing C, D, and E to shift positions. When you go in reverse and delete the fourth item (D), you're left with A, B, C. The second item in that list still is B--nothing has shifted! – Jazimov Apr 13 '18 at 19:29