Msgbox with range output

[Dani_LobP]

Hello,

I would like to know if anyone is able to help with some msgbox issue.

Idea is to have a list of items be displayed in msgbox. I barely use msgbox and i found some info and played a bit and managed to get this code, but not sure how to edit it in a way that the range is from fix start until lastrow (since not always might be same lenght).

And also can't figure out how to add a tittle, to the box or a starting sentence before displaying the list.

MsgBox Join(Application.WorksheetFunction.Transpose(Range("A1:A7").Value), Chr$(10))

Here in the code the list would be created by a macro that do some other stuff... so in this case list goes from A1 to A7.
I use previously 'LastRow2 = Range("A1").End(xlDown).Row' so i could add it to the Msgbox.

As i understand, and the way this works, it displays the 7 items in a list and is fine, but i'd like give a title to can say
"here is all items found in this file" for example, and then be displayed.

For the range, tried (A1:A" & LastRow2 & ") but it won't work.. i guess i'm missing something.

Hope it all makes sense, and look forward for someone to have more knowledge in Msgbox.

Thanks in advance!

 

[mikerickson]

Try this

Dim strPrompt As String, strTitle As String

With Range("A:A")
    strPrompt = Join(Application.Transpose(Range(.Cells(1, 1), .Cells(Rows.Count, 1).End(xlUp)).Value), vbLf)
End With
strTitle = "found items"

MsgBox strPrompt, Title:=strTitle

 

[Dani_LobP]

Great thanks! works as i hoped for.
Any chance i could add also some text before the list? as in
-------------------------
Found items
---------------------------
these are the items: <- this line is what i mean
-
-
-
-
---------------------

 

[mikerickson]

You can build the strPrompt variable as you wish.