excel vba - Concatenate multiple ranges using vba -

i hoping can me problem. basically, have number of ranges need concatenate independently , put values of concatenated ranges different cells. example want to: concatenate values in range a1:a10 , put result in f1 want concatenate range b1:b10 , put result in f2 want concatenate range c1:c10 , put result in f3 etc

i have tried use following macro. stuck; macro seems doing concatenating range a1:a10 , putting results f1 (which want). stores information first concatenation memory when next concatenation, in cell f2 concatenated results of f1 , f2 joined.

i have tried searching lots of forums, since code made myself can't find solution, sure common problem , doing wrong possibly not setting variable correctly.

thanks in advance help,

sub concatenate()      dim x string     dim y string  m = 2 5      y = worksheets("variables").cells(m, 5).value   'above has range information e.g. a1:a10 in sheet variables  each cell in range("" & y & "") 'i.e. range a1:a10     if cell.value = "" goto line1 'this tells macro continue until blank cell reached     x = x & cell.value & "," 'this provides concatenated cell value next  line1:  activecell.value = x  activecell.offset(1, 0).select  next m  end sub 

here concatenaterange. allows add seperator if please. optimized handle large ranges since works dumping data in variant array , working within vba.

you use this:

=concatenaterange(a1:a10) 

the code:

function concatenaterange(byval cell_range range, _                     optional byval seperator string) string  dim cell range dim newstring string dim cellarray variant dim long, j long  cellarray = cell_range.value  = 1 ubound(cellarray, 1)     j = 1 ubound(cellarray, 2)         if len(cellarray(i, j)) <> 0             newstring = newstring & (seperator & cellarray(i, j))         end if     next next  if len(newstring) <> 0     newstring = right$(newstring, (len(newstring) - len(seperator))) end if  concatenaterange = newstring  end function