Macro required for arranging data

[nobleprince]

Hello,

I would like a macro that grabs the relevant data from columns A, E, G, and O and puts the data into column U in the format below (see bottom 2 lines).

For example output in column U should be:

Hi Moss, I wanted to apply for a Graduate Software Engineer in London, Kind regards, John
Hi Louise, I wanted to apply for a Senior Web Developer in Gloucester, Kind regards, Bill

The data should be processed from row 2 to unlimited rows. Please see attached excel.

Thank you in advance,
Ash

[karedog]

Maybe :

Sub Test()
  Dim a, i As Long
  With Sheets("Sheet1")
    a = .Range("A2:O" & .Cells(.Rows.Count, "A").End(xlUp).Row)
    ReDim b(1 To UBound(a, 1), 1 To 1)
    For i = 1 To UBound(a, 1)
        If Len(a(i, 1)) Then b(i, 1) = "Hi " & Split(a(i, 15), " ")(0) & ", I wanted to apply for a " & a(i, 5) & " in " & Split(a(i, 7), " ")(0) & ", Kind regards, " & Split(Split(a(i, 1), "@")(0), ".")(0)
    Next i
    .Range("U2").Resize(UBound(b, 1)).Value = b
  End With
End Sub

[nobleprince]

Works perfect. Thank you very much karedog!

[Winon]

@ karedog,

I Hate You!

Quick, short and sweet Coding.

Well done my friend!

Kind regards.

[karedog]

Winon my old pal, late as usual

Thanks so much for the reppies

[karedog]

You are welcome nobleprince, thanks for marking the thread as solved and rep.points.

Regards