Hi
I have data in approx 800 lines as:Column A Column B
Cook Mark
Jones Peter

Column A has last name, Column B has first name.
I want to joing data of 2 columns to show Firstname Lastname in ColumnA and
then delete Column B

Please advise how to write this code

For each c in range("a2"a800")
c.value= c & " " & c.offset(,1)
next c
columns(2).delete

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"ub" <(E-Mail Removed)> wrote in message
news:380E72FA-255B-4DD7-9F12-(E-Mail Removed)...
> Hi
> I have data in approx 800 lines as:Column A Column B
> Cook Mark
> Jones Peter
>
> Column A has last name, Column B has first name.
> I want to joing data of 2 columns to show Firstname Lastname in ColumnA
> and
> then delete Column B
>
> Please advise how to write this code

You can do without code. Use the formula

=A1 & " " & B1

in column C and copy down. Then copy the formula cells and do an edit,
paste special values back over the formulas. Then delete columns A and B

Robert Flanagan
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

"ub" <(E-Mail Removed)> wrote in message
news:380E72FA-255B-4DD7-9F12-(E-Mail Removed)...
> Hi
> I have data in approx 800 lines as:Column A Column B
> Cook Mark
> Jones Peter
>
> Column A has last name, Column B has first name.
> I want to joing data of 2 columns to show Firstname Lastname in ColumnA
> and
> then delete Column B
>
> Please advise how to write this code

Hi,

Right click your sheet tab, view code and paste this in and run it.

Sub sonic()
lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
Set MyRange = Range("A1:A" & lastrow)
For Each c In MyRange
c.Value = c.Offset(, 1).Value & " " & c.Value
Next
Columns(2).ClearContents
End Sub

Mike

"ub" wrote:

> Hi
> I have data in approx 800 lines as:Column A Column B
> Cook Mark
> Jones Peter
>
> Column A has last name, Column B has first name.
> I want to joing data of 2 columns to show Firstname Lastname in ColumnA and
> then delete Column B
>
> Please advise how to write this code

Thanks , it works


"Mike H" wrote:

> Hi,
>
> Right click your sheet tab, view code and paste this in and run it.
>
> Sub sonic()
> lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
> Set MyRange = Range("A1:A" & lastrow)
> For Each c In MyRange
> c.Value = c.Offset(, 1).Value & " " & c.Value
> Next
> Columns(2).ClearContents
> End Sub
>
> Mike
>
> "ub" wrote:
>
> > Hi
> > I have data in approx 800 lines as:Column A Column B
> > Cook Mark
> > Jones Peter
> >
> > Column A has last name, Column B has first name.
> > I want to joing data of 2 columns to show Firstname Lastname in ColumnA and
> > then delete Column B
> >
> > Please advise how to write this code