Generating a List of Values in Excel (VBA)

I frequently find myself needing to create lists of values I have in Excel in one cell. For example, I might get have a column of IDs and I need to create a comma delimited list of them to put into a SQL Query. I have the following function I put in an XLA that loads with Excel to generate these lists.

 1Public Function GenerateList(r As Range, Optional strDelimiter As String = ", ")
 2 
 3    Dim rCell As Range
 4    Dim strList As String
 5 
 6    For Each rCell In r
 7        strList = strList & rCell.Value & strDelimiter
 8    Next
 9 
10    If strList <> "" Then
11        strList = Left(strList, Len(strList) - Len(strDelimiter))
12    End If
13 
14    GenerateList = strList
15 
16End Function

Now in any cell I can enter something like =GenerateList(A1:A250,”;”) and have a semi-colan delimited list of the values in cells A1 – A250. If you cannot tell from the function, if you do not include a delimiter parameter (the second one), it will default to “comma space”.