Prev: find header then replace header with number of entries below heade
Next: Updating dates
From: James on 12 Feb 2010 00:28
Hi Everyone,

Hope you are all well :)


I have a very simple excel worksheets (Worksheet A and Worksheet B)
which has:

Column A: Empty
Column B: Has entry (First Name) on every cell till row 2000
Column C: Has entry (Last Name) on every cell till row 2000


I know how to get first and last name combined using
(=concatenate(B1," ",C1)) However I have no idea how I can make excel
do this in column A automatically upto row 2000 for worksheet A and B?

Can anyone please help?

Thank you for your help in advance.




James
From: ryguy7272 on 12 Feb 2010 01:40
It's not elegant, but should do what you want:
Sub Concat()

Range("A2").Select
Dim redRng As Range

Set redRng = Range("A1", Range("A2000").End(xlUp))
For Each cell In redRng

If cell.Value <> "" Then
ActiveCell.Offset(0, 0).Select
ActiveCell.FormulaR1C1 = "=RC[1]&"" ""&RC[2]"
ActiveCell.Offset(1, 0).Select
End If

Next cell

End Sub

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"James" wrote:

> Hi Everyone,
>
> Hope you are all well :)
>
>
> I have a very simple excel worksheets (Worksheet A and Worksheet B)
> which has:
>
> Column A: Empty
> Column B: Has entry (First Name) on every cell till row 2000
> Column C: Has entry (Last Name) on every cell till row 2000
>
>
> I know how to get first and last name combined using
> (=concatenate(B1," ",C1)) However I have no idea how I can make excel
> do this in column A automatically upto row 2000 for worksheet A and B?
>
> Can anyone please help?
>
> Thank you for your help in advance.
>
>
>
>
> James
> .
>
 | 
Pages: 1
Prev: find header then replace header with number of entries below heade
Next: Updating dates