Link to home
Start Free TrialLog in
Avatar of Magpie Bavarde
Magpie BavardeFlag for France

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 : User generated image
I thank you very much for your help !
ASKER CERTIFIED SOLUTION
Avatar of Flyster
Flyster
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of 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 !!
I'll look for your example!
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
You're welcome. I'm glad I could help you out!