create list in a message box from range

**jiminic** · 09-08-2008, 12:47 AM

Hi Everyone

I want to show a message that will contain the names of people in a range that is updated regularly. I am using the following code:

Sub Dist_List()
Application.ScreenUpdating = False

Range("D2").Select
    Dim strMsg As String
    Do Until IsEmpty(ActiveCell)
    strMsg = "The following people are currently on the Message Distribution list:" & vbCrLf & vbNewLine
    strMsg = strMsg & "     • " & Range("D2").Value & vbNewLine
    ActiveCell.Offset(1, 0).Select
      Loop
                  
    MsgBox strMsg, vbInformation, "Distribution List"

Application.ScreenUpdating = True
End Sub

The problem I have is , it will only show the person who's name is in cell D2. I have a named range (email2) which currently spans cells D2:D10, but as I said previously, the range can and will grow.

Does anyone have any suggestions?

**royUK** · 09-08-2008, 01:39 AM

Try

Option Explicit

Sub Dist_List()
    Dim rng    As Range
    Dim Cl     As Range
    Dim strMsg As String

    Set rng = Range(Cells(2, 4), Cells(Rows.Count, 4).End(xlUp))

    strMsg = "The following people are currently on the Message Distribution list:" & vbCrLf & vbNewLine
    For Each Cl In rng
        strMsg = strMsg & "     • " & Cl.Value & vbNewLine
    Next Cl
    MsgBox strMsg, vbInformation, "Distribution List"

End Sub