Display in msgbox unique values

Question

I hope you don't mind me asking whether there is an easier way to do what I want to achieve. Whilst my code 'works', it feels quite inefficient and I'd like to understand a quicker way of doing it:

I have a list of account client numbers in column A of my worksheet; and in column Q I have used the UNIQUE formula to determine my unique values. There can't be any more than 5 unique client numbers (but it could be as low as 1) and so I've written the following:

Option Explicit
Sub list()

Dim value1 As Long, value2 As Long, value3 As Long, value4 As Long, value5 As Long
Dim ws As Worksheet

    Set ws = ThisWorkbook.Worksheets("Data")

 value1 = ws.Cells(5, 17).Value
 value2 = ws.Cells(6, 17).Value
 value3 = ws.Cells(7, 17).Value
 value4 = ws.Cells(8, 17).Value
 value5 = ws.Cells(9, 17).Value
 
MsgBox "Clients numbers used: " & vbCrLf & vbCrLf & value1 & vbCrLf & value2 & vbCrLf & value3 & vbCrLf & value4 & vbCrLf & value5, vbInformation
        

End Sub

I presume it would be possible to build an array of the unique values in VBA then only 'print' the ones that actually appear?

Answer 1

Build the message directly from the range of cells.

Option Explicit
Sub list()

    Dim ws As Worksheet, s As String, i As Long
    Dim cel As Range
    Set ws = ThisWorkbook.Worksheets("Data")
    For Each cel In ws.Range("Q5:Q9")
        If Len(cel.value2) > 0 Then
            s = s & vbCrLf & cel.value2
        End If
    Next
     
    MsgBox "Clients numbers used: " & s, vbInformation
            
End Sub