Macro to insert a row.

[jdvdh001]

Hi. I have 2 sheets with similar data.
sheet1 Sheet2
101 101
102 104
103
104
I need the macro to compare the data in sheet 1 and 2 and insert lines in sheet 2 until data match to look like this.
sheet1 sheet2
101 101
102
103
104 104


Thanks

[JBeaucaire]

Assuming:

1) Data on sheet1 is in column A starting at A1
2) Data on sheet2 is in column A starting at A1
3) Data on sheet2 may not be in the correct order to match sheet1 yet
4) All Data on sheet2 is on sheet1, but not all data on sheet1 is on sheet2

--------------
Approach:

1) Add a blank sheet
2) Add a formula to new sheet that does a simple ISNUMBER(MATCH()) test to create the new expanded list using only the values from sheet1, but matching to sheet2 (trust me)

Option Explicit

Sub SpaceSheet2Data()
Dim LR As Long

LR = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row

With Sheets.Add
    With .Range("A1:A" & LR)
        .FormulaR1C1 = "=IF(ISNUMBER(MATCH(Sheet1!RC, Sheet2!C, 0)), Sheet1!RC, """")"
        .Copy
    End With
    
    Sheets("Sheet2").Range("A1").PasteSpecial xlPasteValues
    Application.DisplayAlerts = False
    .Delete
End With

End Sub

3) Copy the new list of values from the new sheet to sheet2 replacing the original data
4) Delete the added sheet

[jdvdh001]

Thanks so much it works 100%

[JBeaucaire]

I've marked this thread as SOLVED for you.
Next time, select Thread Tools from the links above to mark a thread as SOLVED. Thanks.