asked on

VBA : compare 2 columns and complete the shortest one

Hello the experts,

I want a generic code that will work on several very different sheets. One of them ("Full") has more data than the other ("Miss"). So far I have this from internet and switch a little to get closer to my goal. That part does half of the job (compare then add an empty row) :

 Sub WIPInsertMissing()
    Dim lastrow As Long, i As Long, j As Long
    lastrow = Sheets("Full").Range("A" & Rows.Count).End(xlUp).row
        j = 1
    For i = 1 To lastrow
        If Sheets("Miss").Cells(j, 1).Value <> Sheets("Full").Cells(i, 1).Value Then
            Sheets("Miss").Rows(j).Insert Shift:=xlDown
        End If
        j = j + 1
    Next i
End Sub

Open in new window

... but it doesnt always work and I can't figure why. I wouldnt mind some help to correct the code and add the part where it copies the missing data ;) to get something like this :

I thank you very much for your help !

ASKER CERTIFIED SOLUTION

Flyster

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Magpie Bavarde

ASKER

Hello and thank you,

I think the line you've added will work but the code isnt fixed, one one example it's working, on another it duplicates starting row 13 (there's always something happening around row 12-14 it seems :p), and as I dont understand the basics I can only go with test and see what happens

I will post examples tomorrow, I'm too tired

Thanks for your help !!

Flyster

I'll look for your example!

Magpie Bavarde

ASKER

Hello again,

I've managed to have it work the way I want, I'll open another request when I'm stuck with the little issues of the code ;)

The line you added works just fine when the sheet is "risk free"

Thank you very much for your help !

Kind regards

Mélanie

Flyster

You're welcome. I'm glad I could help you out!