For Next loop is missing rows...


Board Regular
Dec 28, 2010

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:

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")
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False 
    For i = 1 To DKCount
        If Cells(i, "B") = "D" Then
            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!

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
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
Upvote 0
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!!
Upvote 0
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.
Upvote 0

since youve used this formula
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
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back