Msgbox

[John Davis]

Hello all:

I have the following code snipet:

m = Array(Range("X1:X" & lr2).Value)

MsgBox ("" & m)

I'm trying to get the values contained in X1:X & lr2 to show in a msgbox. How would I code that? Any help appreciated.

 

[VoG]

MJaybe like this

m = Range("X1:X" & lr2).Value
For i = LBound(m, 1) To UBound(m, 1)
    msg = msg & vbNewLine & m(i,1)
Next i
MsgBox msg

 

[Kenneth Hobson]

Test on a blank sheet:

Sub t()
  Dim m As Variant, lr2 As Long
  lr2 = 10
  Range("X1:X10").Formula = "=Row() & "", "" & Column()"
  m = WorksheetFunction.Transpose(Range("X1:X" & lr2).Value)
  MsgBox Join(m, vbCrLf)
End Sub

 

[HalfAce]

Same idea as Mr. Hobson (but for an unknown row of the last cell with data.)

Sub MsgBoxDemo()
Dim lr2 As Long, ArryRng As Variant
lr2 = Range("X" & Rows.Count).End(xlUp).Row
ArryRng = Range("X1:X" & lr2)
ArryRng = Application.WorksheetFunction.Transpose(ArryRng)
MsgBox Join(ArryRng, ", ")
End Sub

Hope it helps.

 

[Rick Rothstein]

This one-liner should do what you asked...

MsgBox Join(WorksheetFunction.Transpose(Range("X1:X" & lr2)), vbLf)

 

[HalfAce]

This one-liner should do what you asked...

MsgBox Join(WorksheetFunction.Transpose(Range("X1:X" & lr2)), vbLf)

This would work if you specify your variable (lr2) first. Like so:

lr2 = Range("X" & Rows.Count).End(xlUp).Row
MsgBox Join(WorksheetFunction.Transpose(Range("X1:X" & lr2)), vbLf)

Or, if you really want it to be a one liner, you can do this:

MsgBox Join(WorksheetFunction.Transpose(Range("X1:X" & Range("X" & Rows.Count).End(xlUp).Row)), vbLf)

Hope it helps.

 

[John Davis]

Thanks for all the quick responses. All solutions work perfectly.

 

[Rick Rothstein]

This one-liner should do what you asked...

MsgBox Join(WorksheetFunction.Transpose(Range("X1:X" & lr2)), vbLf)

This would work if you specify your variable (lr2) first. Like so:

lr2 = Range("X" & Rows.Count).End(xlUp).Row
MsgBox Join(WorksheetFunction.Transpose(Range("X1:X" & lr2)), vbLf)

The OP started his message with... "I have the following code snipet", so I made the assumption that, since his code snippet referred to lr2, he must have set lr2 in the code prior to that which he posted. My reference to "one-liner" was to handle the code snippet he was after, not his entire code module (of which we know nothing about).

 

[HalfAce]

A fair assumption indeed. That's the drawback of not seeing the OP's original code. Assuming that snippet was the entire routine, (which I did), then your single line solution was (in my opinion) the best.

I only went with the one I suggested as it seemed to be close enough to the original snippet as to be intuitive to the OP. I would rather show a little longer way to do something that the OP would likely recognize & understand than to do it in a way that you and I might understand, but isn't intuitive to the OP.