merging ranges

R

Rich Cooper

I have two ranges. One with last names and one with first names. I want to
take those two ranges and make them one new range. i want that new range to
look like lastname, firstname. I would think this is very simple to do. I
have been trying but i can't get it to work.
 
T

Tom Ogilvy

Lastnames in column A
Firstnames in column B
Put the joined names in Column C

Dim rng as Range, cell as Range
set rng = Range(Cells(1,1),Cells(1,1).End(xldown)
for each cell in rng
cell.offset(0,2).Value = cell.value & ", " & cell.offset(0,1).Value
Next

a formula: in C1

=trim(A1) & ", " & trim(B1)

then drag fill down the column

Same approach in code:
Dim rng as Range, cell as Range
set rng = Range(Cells(1,1),Cells(1,1).End(xldown)
rng.offset(0,2).Formula = "=trim(A1) & "", "" & trim(B1)"
'replace the formula with its results.
rng.offset(0,2).Formula = rng.Offset(0,2).Value
 
Top