VBA : compare 2 columns and complete the shortest one

Magpie Bavarde
Magpie Bavarde used Ask the Experts™
on
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 : FillsMissingMacro.png
I thank you very much for your help !
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
I tried your code and it seemed to work. Here is an update which will give you the values on the "Miss" sheet:
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
            Sheets("Miss").Cells(j, 1).Value = Sheets("Full").Cells(i, 1).Value 'This line added for values
        End If
        j = j + 1
    Next i
End Sub

Open in new window

Paul
Magpie BavardeExecutive Assistant

Author

Commented:
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!
Magpie BavardeExecutive Assistant

Author

Commented:
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!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial