Your technology certification is waiting. Enroll in Cloud Class ®
Dim i As Long, c As String
For i = 3 To 7
c = c & "; " & Cells(i, 1)
c = Right(c, Len(c) - 2)
Cells(i - 1, 2) = c
Open in new window
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
' 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
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))
ConcRange = Mid$(ConcRange, Len(Delim) + 1)
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.
Please enter a first name
Please enter a last name
Must be at least 4 characters long.
Join and Comment
From novice to tech pro — start learning today.
Premium members can enroll in this course at no extra cost.