Share via

Find Duplicates and Insert a row before duplicate values

Anonymous
2013-05-06T12:58:23+00:00

I use a macro in an excel which identifies duplicates and inserts a row before the duplicate values,  the trouble am facing is the macro only identifies upto 2 duplicates and inserts a row for every 2 duplicates.  So if I had 6 duplicates, it would insert a row after every 2 duplicates. I need it group a set of duplicates, Can someone help me modify the macro in such a way that it works for a n duplicate value?    

Option Explicit

Sub Insert_Row()

Dim LASTROW As Long

Dim I As Long

LASTROW = Range("A" & Rows.Count).End(xlUp).Row

For I = LASTROW + 1 To 1 Step -1

If ((Cells(I, "A") = Cells(I + 1, "A")) And (Cells(I, "A") <> "")) Then

Rows(I).Insert

End If

Next I

End Sub

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous
2013-05-06T13:11:59+00:00

Hi,

Try this one.

Sub insertrow()

Dim x as Long

MyColumn = "A"

For x = Cells(Rows.Count, MyColumn).End(xlUp).Row To 2 Step -1

    If Cells(x - 1, MyColumn) <> Cells(x, MyColumn) Then Rows(x).Insert

Next x

End Sub

Was this answer helpful?

5 people found this answer helpful.
0 comments

4 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2013-05-06T16:05:50+00:00

    Hi Mike,

    Actually you right... It works perfectly fine... thanks... must be some formatting issue or with my data.. will look into that...

    Shankar

    Was this answer helpful?

    0 comments
  2. Anonymous
    2013-05-06T15:53:22+00:00

    Hi

    try this :

    Assuming the above mention range is in B1:B10.

    create a helper column, in cell A2 type this formula :=IF(B1=B2,"",NA()) & copy down

    select range A1:A10 ,Press F5 ,click >Special>formula >uncheck all except Error press ok

    then insert rows

    Hope this helps

    Regard

    Nauman

    Was this answer helpful?

    0 comments
  3. Anonymous
    2013-05-06T15:16:37+00:00

    Hi Mike,

    Thank you, this works for the duplicates except that it inserts a row after every cell, even for the non duplicates....

    Shankar

    Hi,

    It doesn't for me, if I have this data in col A

    1
    1
    1
    1
    2
    3
    3
    3
    3
    3<br><br><br> <br><br><br>Then I get this, what result do you expect?<br><br><br> <br><br><br> <br><br><br><br> 1 <br> --- <br> 1 <br> 1 <br> 1<br><br><br>Space <br> 2<br><br><br>space <br> 3 <br> 3 <br> 3 <br> 3 <br> 3

    Was this answer helpful?

    0 comments
  4. Anonymous
    2013-05-06T13:40:22+00:00

    Hi Mike,

    Thank you, this works for the duplicates except that it inserts a row after every cell, even for the non duplicates....

    Shankar

    Was this answer helpful?

    0 comments