VBA Automatically concatenate cell data based on data, and prefix with specific word.

Hi,

Could anyone help me with what may be a simple solution using VBA please? What I am trying to do is to take all values from (for example) cells A2 to G2 and concatenate those values into cell H2, seperated by a comma, or to leave a comma out (skip it) if no data exists in any of those cells:

For example if all cells have data:

A2	B2	C2	D2	E2	F2	G2	H2
I	Am	A	Real	Piece	Of	Work	I, Am, A, Real, Piece, Of, Work

For example if NOT all cells have data:

A2	B2	C2	D2	E2	F2	G2	H2
I	Am	A		Piece		Work	I, Am, A, Piece, Work

But I would like to do this on the worksheet_change function so that it updates H2 each time it leaves one of the cells in the row, data or not.

I have an additional requirement that if cell A2 contains a specific word/number, the finished concatenation in cell H2 is prefixed with a certain word, i.e. "Hi", so that the result would be:

Hi, I, Am, A, Real, Piece, Of, Work

Any help would be greatly appreciated, I have searched all over this board to find a result, but none that work with the worksheet_change function, or at least without having to call a macro each time.

Thanks in advance!
Jamie

Please Login or Register  to view this content.

And then just use the function in the cell you would like it in. Alsom change the "/" to ",".

Here's one way

Please Login or Register  to view this content.

Me again Richard, sorry after testing it all, the code seemed to work fine until when all data in cells (A2 to G2) was removed, and it came up with this error:

Run-time error '5':
Invalid procedure call or argument

Is there any way to eliminate this error please?