Need help with a script in Excel to insert rows above when duplicates are found

Hi – I run a report out of SQL and into Excel everyday and have a reference (in Col B) which is sometimes duplicated. The reference is numeric and never the same and there can be one that more duplicates of the numeric. For Example:

1,2,2,3 first day

4,5,5,6,6,6,7 second day

I need a script in VBA that will insert a row above where the first duplicate is found so as above:



Please can someone help before my head explodes? Thanks

Participant Asked 4 hours ago in VBA: Basics.
Add Comment
1 Answer(s)

Caveat: Cells B1 and B2 Must not be duplicates.

Note! Change sheet name (“Sheet1”) to your sheet name.

RE: Need help with a script in Excel to insert rows above when duplicates are found



Sub insertrow()

Dim ws As Worksheet
Set ws = Worksheets(“Sheet1”) ‘ Change this to your sheet name
Application.ScreenUpdating = False

Do Until IsEmpty(ActiveCell)

If ActiveCell.Value = ActiveCell.Offset(-1).Value Then

Selection.Insert Shift:=xlDown
ActiveCell.Offset(2, 1).Select

If ActiveCell.Value = ActiveCell.Offset(1).Value Then
Exit Do
End If


End If
End Sub

Expert Answered 43 mins ago.
Add Comment
  • Found this useful?

    Please share using the share button above.

    If you found the answer is best answer for your question, Please mark as 'best answer' by clicking on the right tick mark icon at the left side of the answer.

    Found the answer useful and wants to credit the user, then vote the answer (vote up).

  • Your Answer

    By posting your answer, you agree to the privacy policy and terms of service.