copying only last row data

Posted by jason on December 23, 2001 6:42 PM

I have name information below

Name
MR IAN WILLIAM KERR
MR G A LYMBERY
MRS Sandy lyn Roberts
Jim Spencer

using text to coloums the data is then seperated and i want to cut the last piece of data (surname)from each row and move it to the front so that when it is imported to access a surname search can be used.

but as the amount of rows differs due to different name lengths this takes forever.

basically cut the last piece of data from each row and past tit to the front.

Name
KERR MR IAN WILLIAM
LYMBERY MR G A LYMBERY
Roberts MRS Sandy lyn
Spencer Jim

Tadar.

thanks J

Posted by Tybalt on December 24, 2001 1:42 AM

Assuming the first column is column A :-

Sub Move_Last_Column()
Dim rng As Range, cell As Range
Application.ScreenUpdating = False
Columns(1).Insert
Set rng = Range([B1], [B65536].End(xlUp))
For Each cell In rng
cell.End(xlToRight).Cut Cells(cell.Row, 1)
Next
Columns(1).Columns.AutoFit
End Sub