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
– Mathieu Guindon, Commented Apr 3, 2017 at 20:18

Community · Accepted Answer · 2017-05-23 12:32:29Z

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.

Vinicius · Accepted Answer · 2017-04-03 20:36:53Z

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.

Subodh Tiwari sktneer · Accepted Answer · 2017-04-03 20:14:28Z

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)

Collectives™ on Stack Overflow

VBA identify last cell in range in concatenation function

3 Answers 3

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Linked

Related