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”.