MsgBox display layout

[julhs]

Using helper cells (“B117:B120”), the code I have below does display the MsgBox in the format that I want.
However, is there any way to do this without hard coding the individual cells but use a Named Range for the helper cells instead?
If there is, nothing I’ve tried works
My code is this,

Sub MsgBoxMissingNames()
MsgBox "Missing names are:-" _
& vbCrLf & " " & Range("B117").Value _
& vbCrLf & " " & Range("B118").Value _
& vbCrLf & " " & Range("B119").Value _
& vbCrLf & " " & Range("B120").Value, vbOKOnly
Range("MissingNames").Select ‘This is the helper cells Named Range
End Sub

Nearest thing I came up with as a way of compromise was:

>>
Dim r As Range, ary
Set r = Sheets("Sheet1").Range("MissingNames")
With Application
MsgBox " Missing names are:-" & vbCrLf & _
Join(.Transpose(r), " & ")
>>

 

[AlphaFrog]

MsgBox " Missing names are:-" & vbCrLf & " " & _
Join(.Transpose(r), vbLf & " ")

 

[DanteAmor]

Try:

Sub MsgBoxMissingNames()
  MsgBox "Missing names are:-" & vbCr & Join(Application.Transpose(Range("MissingNames").Value), vbCr)
End Sub

 

[julhs]

Thank you both for response.
As both work for me, all be it with different syntax and I can only mark one as a solution, all I can do is mark both as “Like”

Thank you both again.

Julhs