Preparing spreadsheet for mail merge

[reseng]

Hi
I have a spreadsheet with multiple email addresses. If a row has multiple email addresses in cells I & J, I want to be able to insert a row below that row and copy the cells containing names (first and last in cells B & C respectively) from above and insert the second email address from cell J (the row above) in cell I in the inserted row.
Also some email addresses in cell J don't have first or last name, just a company name in cell D. I want the company name moved to first name (cell B) and email address from cell J to cell I.
Can anyone help please?
Thanks a lot for your help in advance.

 

[mumps]

Try:

Sub InsertData()
    Application.ScreenUpdating = False
    Dim lRow As Long, v As Variant, i As Long
    lRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    v = Range("B2", Range("B" & Rows.Count).End(xlUp)).Resize(, 9).Value
    For i = UBound(v) To LBound(v) Step -1
        If v(i, 9) <> "" And v(i, 8) <> "" Then
            Rows(i + 2).EntireRow.Insert
            If v(i, 1) <> "" And v(i, 2) <> "" Then
                Cells(i + 2, 2).Resize(, 2).Value = Array(v(i, 1), v(i, 2))
                Cells(i + 2, 9) = v(i, 9)
            ElseIf v(i, 1) = "" And v(i, 2) = "" Then
                Cells(i + 2, 2) = v(i, 3)
                Cells(i + 2, 9) = v(i, 9)
            End If
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

 

[reseng]

Thanks for that. works like a charm.
Can you please help me with the second part of the thread.
row 9 only has coname in cell D, no first or last name (B & C cells) and email address in cell J. I want to move the Coname to cell B and email address to cell J.
Really appreciate your help.
Cheers

 

[mumps]

I thought that you wanted to work with only the rows that had email addresses in both column I and J. Row 9 doesn't have an email addresses in column I. I also thought you wanted to copy the email address from column J to column I in the inserted row. Please clarify in detail.