=MyCnctnt(($A$1:$A1;$A3:$A$100);СИМВОЛ(10))

=MyCnctnt(($A$1:$A1;$A3:$A$100);CHAR(10))

Public Function MyCnctnt(R As Range, Dlmt As String)
Dim rc As Range, i As Integer
i = R.Cells.Count
For Each rc In R.Cells
    MyCnctnt = MyCnctnt & rc.Text
    i = i - 1
    If i > 0 Then
        MyCnctnt = MyCnctnt & Dlmt
    End If
Next rc
End Function

=MyCnctnt(($A$1:$A5;$A7:$A$100);" ")

=MyCnctnt(($A2:$A$100);" ")

Private Sub Workbook_Open()
    Application.Calculation = xlManual
    Workbooks.Open Filename:= "Файл который нужно открыть на самом деле"
    ThisWorkbook.Close
End Sub