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.
Public Function GenerateList(r As Range, Optional strDelimiter As String = ", ")
Dim rCell As Range
Dim strList As String
For Each rCell In r
strList = strList & rCell.Value & strDelimiter
If strList <> "" Then
strList = Left(strList, Len(strList) - Len(strDelimiter))
GenerateList = strList
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”.