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

[jamieswift1977]

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