VBA identify last cell in range in concatenation function

Question

I'm writing a function that will take a range of cells and return their values in a single cell separated by commas.

So a list of three or four stock tickers would look like this:

GOOG, TSLA, APPL

The easy way to do this is:

For Each cell In rng
    Tickers = Tickers & cell.Value & ", "
Next cell

The problem, of course, is that the last value will have a comma which is undesirable.

So I thought it might be possible to include an If statement that would change the output for the last cell like this:

For Each cell In rng
    If cell = rng.Count - 1 Then
        Tickers = Tickers & cell.Value & "! "
    Else
        Tickers = Tickers + cell.Value & ", "
    End If
Next cell

However, this does not seem to be working I assume because cell and rng are possibly not simple integers so count-1 doesn't make sense.

So what would be the best way to go about this? Is there a method for rng that would allow the If statement to know if the cell was indeed the last in the range?

*I assume because cell and rng are possibly not simple integers* - you could start by forcing yourself to declare every variable with an explicit type, by specifying `Option Explicit` at the top of every module. `rng` is likely a `Range` object, and that makes `cell` a `Range` object as well. Kudos for explicitly using `.Value` instead of making a *default property* call, which would have read pretty much exactly as if `cell` *was* a "simple integer". — Mathieu Guindon, Apr 03 '17 at 20:18

score 3 · Answer 1 · edited May 23 '17 at 12:32

The single best way to join a bunch of strings with a delimiter, is to use the VBA.Strings.Join function, which requires a single-dimensional array and a delimiter. So you first need to turn your input range into a 1D array.

This answer explains how to get a 1D array out of a Range.

So assuming your input range is contiguous, you can do that and then use the VBA.Strings.Join function to concatenate the results.

For example if your values are in Sheet1!A1:A10, this one-liner gets you the 10 values separated by a comma:

VBA.Strings.Join(Application.Transpose(Sheet1.Range("A1:A10").Value),", ")

If the range is not contiguous, then you Dim an array using the Count of cells in your source range, iterate each cell with For Each like you're doing, populate the array, and then your function can return Join(theValues, ", ").

Then, if you want to append an exclamation mark, you don't need to care about which cell is the last one - you just append an exclamation mark to your string and you're done.

score 0 · Answer 2 · answered Apr 03 '17 at 20:14

You may try this....

Function CombineCellContent(ByVal Rng As Range) As String
Dim cell As Range
Dim str As String
For Each cell In Rng
    If str = "" Then
        str = cell.Value
    Else
        str = str & ", " & cell.Value
    End If
Next cell
CombineCellContent = str
End Function

Then assuming your data is in the range A1:A3, you may use this function like below...

=CombineCellContent(A1:A3)

score 0 · Accepted Answer · answered Apr 03 '17 at 20:36

I know two ways to do this, first is:

For Each cell In rng
    times = times + 1
    If times = rng.Count Then
        Tickers = Tickers + cell.Value & "! "
    Else
        Tickers = Tickers + cell.Value & ", "
    End If
Next cell

In this case every time you do the loop the variable times increase one, so when times equal to rng.count the first if will be selected.

And the other way is not use For each

For cell = 1 to rng.Count
If cell = rng.Count Then
    Tickers = Tickers + Cells(cell,column).Value & "! "
Else
    Tickers = Tickers + Cells(cell,column).Value & ", "
End If
Next cell

Where column variable is the number of the column where is the data.

VBA identify last cell in range in concatenation function

3 Answers3