i want to merge cells with vba

im new to this and i want to ask one question if anyone can help. im working with excel 2003 and what i wan开发者_如何学Got is that i have one column which at cell A1 i have name and in cell A2 i have surname, in cell A3 i have name and in cell A4 i have surname and so on.

what i want to do is to merge cell A1 with A2 and then A3 with A4 and so on. its about 3000 rows.

is there any vba code to do it automatically?

You don't really need a VBA program for this, just use Excel formulas as so:

1. In the column next to your names fill a sequential list of numbers starting with 1. So this should have 1, 2, 3, 4 and so forth. You can go to the bottom of your A column, but half-way will be fine. TO do this quickly, type a 1 in the first row (B1), then in the second (B2) add a formula: =B1+1. Then copy this formula all the way down B column to the extent of the A column. The trick here is to go to B2, select Edit/Copy, then move to A column and Ctrl-Down, move to the B column and then use Shift+Ctrl-Up, then paste.

2. In C column you will put a formula to merge two cells together from A column, as so:

   =INDEX($A$1:$A$<ENDROW>,(B1-1)*2+1,1)&" "&INDEX($A$1:$A$<ENDROW>,(B1-1)*2+2,1)

   Where <ENDROW> is the last row in A.

   Copy this formula down at least half the rows of the A column. If you go beyond this, you will get #REF! error; just clear those cells.

3. Now you want to copy the formulas in C to another column, say D. Select them, use Edit/Copy, then move to D1 and right-click and choose "Paste special". In the dialog click on "Values" and then "OK". Now you have a column with the values of every two rows concatenated together.

to do this in vba:

Sub nameMerger()

Range("A1").Select

While ActiveCell.Value <> vbNullString

    ActiveCell.Value = ActiveCell.Value & " " & ActiveCell.Offset(0, 1).Value
    ActiveCell.Offset(0, 1).EntireColumn.Delete
    ActiveCell.Offset(0, 1).Select

Wend

End Sub

Tested and works