Hi, I have the below code which joins all of my cells into one column, now I would like to add a "|" separator at the end of each cell before they are joined into one cell. If someone could advise the best way to do this in this code that would be great.
At the minute I am creating a new sheet and using this code but I think I should be able to do it in the original sheet. Any help is greatly appreciated
Code:
Sub JoinCells()
Dim lastrow As Long
lastrow = Range("A" & Rows.Count).End(xlUp).row
Columns("A:A").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A3").Select
ActiveCell.FormulaR1C1 = "ISI File"
Range("A4").Select
ActiveCell.FormulaR1C1 = Join(Application.Index(Range("B4:AEN4").Value, 1, 0), "")
Range("A4").AutoFill Destination:=Range("A4:A" & lastrow)
End Sub
At the minute I am creating a new sheet and using this code but I think I should be able to do it in the original sheet. Any help is greatly appreciated
Code:
Range("A4").Select
ActiveCell.FormulaR1C1 = "=POL!RC&""|"""
Range("A4").Select
Selection.AutoFill Destination:=Range("A4:AEO4"), Type:=xlFillDefault
Range("A4:AEO4").Select
Selection.AutoFill Destination:=Range("A4:AEO" & lastrow), Type:=xlFillDefault