For Next loop is missing rows...

[Cease]

Hi,

I have a simple macro I use to remove duplicate entries.

As an example I have two Adjacent Columns:
Col A is the data that may have duplicates.
Column B is a simple formula:

=IF(A2=A1,"D",0)

Cell C1 is a countif() to determine the amount of total rows.

Then I use the following macro to delete the duplicate rows ( the data in Column A is sorted numerically and the formulas in column B are populated outside of this macro. This macro pastes the values in column B, so that the "D" value remains true to the row it's referencing, then [in theory] deletes any row where the value in Column A is a duplicate.):

Sub DupeKill()
    DKCount = Range("C1")
 
    Columns("B:B").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
 
    Application.CutCopyMode = False 
    For i = 1 To DKCount
        If Cells(i, "B") = "D" Then
            Rows(i).Select
            Selection.Delete shift:=xlUp
        End If
    Next i                        
End Sub

The problem I'm having is this macro misses several rows, all of which are when Column A has more than 2 duplicate values. In these cases it deletes all except the first of these rows.

Any idea what might be causing this?
Running the above macro twice does catch the ones that are missed from the first run, but I don't understand how the macro is skipping a row when the value in column B is "D", regardless what is above or below it.

Thanks in advance for any help/advice!

 

[njimack]

When deleting rows, you need to work backwards, per the below (untested) code. Having said that, if you're simply deleting all rows where column B = "D", it would be far quicker using autofilter, and deleting all visible rows.

Sub DupeKill()
    Dim DKCount As Long
    Dim i As Long
    
    DKCount = Range("C1")
 
    Columns("B:B").Formula = Columns("B:B").Value
    
      For i = DKCount To 1 Step -1
            If Cells(i, 2) = "D" Then Cells(i, 2).EntireRow.Delete
    Next i
End Sub

 

[Cease]

Hey njimack,

That nailed it. Works perfectly and didn't miss a single one.

Does it have to run from bottom to top because of the way the rows are deleted?

Any way about it thank you so much for your time and help!!

 

[njimack]

You're welcome.

Suppose you have 3 rows of data and you use a For Next loop to work your way down and delete each row.

When i = 1, row 1 will be deleted. This means rows 2 and 3 move up one row to become rows 1 and 2 respectively. i is then incremented from 1 to 2, which means row 2 is deleted, but this was previously your row 3. Therefore, the 2nd row of your original data was never evaluated by the code. The only way around this is to work from the bottom upwards.

As I said in my previous post, in the specific example you've asked about, a quicker/better approach would be to filter the data and then use SpecialCells(xlVisibleCellsOnly) to delete them all at once.

 

[Jonmo1]

Or..

since youve used this formula
=IF(A2=A1,"D",0)
In column B,
Then all of column B is either D or 0
And you want to delete the D's...

Use this line after the line converting formulas to values..

Columns("B:B").SpecialCells(xlCellTypeConstants, 2).EntireRow.Delete