Sub ConcatAndFormat()
' Dimension the variables we will use in the process
Dim lngRows As Long
Dim intStartRow As Integer
Dim intTitleColumn As Integer
Dim intArtistColumn As Integer
Dim intTitleAndArtistColumn As Integer
Dim shtProcessing As Worksheet
Dim objCell As Object
Dim intTitleLength As Integer
Dim intArtistLength As Integer
Dim rngConcatAndFormat As Range
' Specify which sheet is being used
Set shtProcessing = Sheet1
' Which row should the process start with?
intStartRow = 2
' Which column is the song title in?
intTitleColumn = 1
' Which column is the artist name in?
intArtistColumn = 2
' Which column will we perform the join and formatting in?
intTitleAndArtistColumn = 3
' Using the specified sheet ...
With shtProcessing
' Calculate the row we should finish on
lngRows = .Cells(.Rows.Count, 1).End(xlUp).Row
' Specify the range we will be writing to
Set rngConcatAndFormat = .Cells(intStartRow, intTitleAndArtistColumn).Resize(lngRows - 1, 1)
' Iterate through the cells in the range
For Each objCell In rngConcatAndFormat.Cells
' Concatenate the Title with a carriage return and the artist name
objCell.Value = _
.Cells(objCell.Row, intTitleColumn).Value & _
Chr(10) & _
.Cells(objCell.Row, intArtistColumn).Value
' Calculate the length of the title
intTitleLength = Len(.Cells(objCell.Row, intTitleColumn).Value)
' Calculate the length of the artist name
intArtistLength = Len(.Cells(objCell.Row, intArtistColumn).Value)
' Do the formatting ...
With objCell
' ... of the title
With .Characters(Start:=1, Length:=intTitleLength).Font
.Name = "Calibri"
.FontStyle = "Bold"
.Size = 12
End With
' ... of the artist name
With .Characters(Start:=intTitleLength + 2, Length:=intArtistLength).Font
.Name = "Calibri"
.FontStyle = "Italic"
.Size = 8
End With
End With
Next objCell
' Adjust the column width to fit
With .Cells(1, intTitleAndArtistColumn)
.ColumnWidth = 80
.EntireColumn.AutoFit
End With
' Readjust the row heights
With rngConcatAndFormat
.EntireRow.AutoFit
End With
End With
' Set sheet and range objects to nothing
Set shtProcessing = Nothing
Set rngConcatAndFormat = Nothing
End Sub