How to concatenate 2 cells for each cell in a range

[gt213]

I have a worksheet with data in it from Cols A-U and a variable number of rows.

In col V I want to concatenate the data in cells A and E for every row that there is data.

So far I have set a range that is all the cells in col V that I want to concatenate A and E for but I'm not sure how to complete this.

Here is the relevant code I have so far.

With ws1
i = .Range(.Range("A1"), .Range("A1").End(xlDown)).Rows.Count
End With
Set HelperRange = ws1.Range(ws1.Cells(2, 22), ws1.Cells(i, 22))

 

[Joe4]

Do you want the VBA code to put the formula in column V, or just the resulting value in column V?

 

[gt213]

I'd probably prefer just the value, but either would work.

 

[Joe4]

Give this a shot and see if it does what you want:

Sub MyCombine()

    Dim lastRow As Long
    
'   Find lastRow in column A with data
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row
    
'   Populate formula
    Range(Cells(2, "V"), Cells(lastRow, "V")).FormulaR1C1 = "=RC[-21]&RC[-17]"
    Range(Cells(2, "V"), Cells(lastRow, "V")).Value = Range(Cells(2, "V"), Cells(lastRow, "V")).Value
    
End Sub

 

[gt213]

Thanks Joe that worked perfectly.

 

[Joe4]

You are welcome!