Hi there,

Apologies if this has been answered already - I had a search but couldn't find a solution.

What would appear to be a seemingly simple task has beaten me. I am trying to write a small piece of code that will make use of a cell reference from another cell. The problem is that the location and relative offsetting of these cells will be different every time, so I can't make use of any absolute or offsetting in the code.

Here is what I am trying to automate. I have a column of data. I need these cells to be listed together with commas separating them, thus:

red
blue
green
yellow

...becomes...

red,blue,green,yellow

At the moment, using columns A and B as an example, I do this

A B
red =A1
blue =B1 & "," & A2
green
yellow

I complete the list by pulling the fill handle down the column from the formula in B2. It's quick and easy, but I do this so often that I want to automate it.

I had planned to do the first cell selection manually, select the cell below and have a button play some code to fill in the formula rather than have to type it out. The nature of the beast means that these two columns could be anywhere. I can return the cell reference in B1 as a variable, and go through the faff of using LEFT or RIGHT to find the number portion of the reference, add one to that number and then reapply the result into the B2 formula, but to me that's messy. I am sure you all know what I mean! I want an elegant solution to this and the fact that I can't find it has become the issue here.

Anyone have any suggestions? Essentially I think I need to convert the range reference into a cells reference but found nothing to help do this.

Thanks in advance,
Rob.