Syntax in excel 2007 for this

I have a column with names in it
say 200 rows

I need to form a string such that the last row colum b
has a;b;c;d;e;f;g;h; ......
I need the code to do this

Who is Participating?
kgerbConnect With a Mentor Chief EngineerCommented:
Hello SMadhavi,
This should do the trick.  Change the numbers 3 and 7 to the row numbers where your list of names starts and ends.

Sub Concat()
Dim i As Long, c As String
For i = 3 To 7
    c = c & "; " & Cells(i, 1)
Next i
c = Right(c, Len(c) - 2)
Cells(i - 1, 2) = c
End Sub

Open in new window

If this is adhoc and does not need to be repeated. I have to do this many times say to create a SQL in statement built from an extract. You Can use a formula like this an a blank column on the sheet.
In the second row of data, =( A1 & ";" & A2 ) and copy this formula down to the last row. The string in the last row will be what you want.
Saqib Husain, SyedEngineerCommented:
Try this code

Sub listcat()
Dim rw As Long, lst As String, cel As Range
rw = Range("A" & Rows.Count).End(xlUp).Row
lst = [a1]
For Each cel In Range("A2:A" & rw)
lst = lst & ";" & cel
Next cel
Cells(rw, 2) = lst
End Sub
Patrick MatthewsCommented:
Or as a function:

Function ConcRange(Substrings As Range, Optional Delim As String = "", _
    Optional AsDisplayed As Boolean = False, Optional SkipBlanks As Boolean = False)
    ' Function by Patrick Matthews, Matt Vidas, and rberke

    ' Concatenates a range of cells, using an optional delimiter.  The concatenated
    ' strings may be either actual values (AsDisplayed=False) or displayed values.
    ' If NoBlanks=True, blanks cells or cells that evaluate to a zero-length string
    ' are skipped in the concatenation
    ' Substrings: the range of cells whose values/text you want to concatenate.  May be
    ' from a row, a column, or a "rectangular" range (1+ rows, 1+ columns)
    ' Delimiter: the optional separator you want inserted between each item to be
    ' concatenated.  By default, the function will use a zero-length string as the
    ' delimiter (which is what Excel's CONCATENATE function does), but you can specify
    ' your own character(s).  (The Delimiter can be more than one character)
    ' AsDisplayed: for numeric values (includes currency but not dates), this controls
    ' whether the real value of the cell is used for concatenation, or the formatted
    ' displayed value.  Note for how dates are handled: if AsDisplayed is FALSE or omitted,
    ' dates will show up using whatever format you have selected in your regional settings
    ' for displaying dates.  If AsDisplayed=TRUE, dates will use the formatted displayed
    ' value

    ' SkipBlanks: Indicates whether the function should ignore blank cells (or cells with
    ' nothing but spaces) in the Substrings range when it performs the concatenation.
    ' If NoBlanks=FALSE or is omitted, the function includes blank cells in the
    ' concatenation.  In the examples above, where NoBlanks=False, you will see "extra"
    ' delimiters in cases where the Substrings range has blank cells (or cells with only
    ' spaces)
    Dim CLL As Range
    For Each CLL In Substrings.Cells
        If Not (SkipBlanks And Trim(CLL) = "") Then
            ConcRange = ConcRange & Delim & IIf(AsDisplayed, Trim(CLL.Text), Trim(CLL.Value))
        End If
    Next CLL

    ConcRange = Mid$(ConcRange, Len(Delim) + 1)
End Function

Open in new window

That would allow a worksheet formula such as:

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.