moving alternate rows
 

I am currently working on a spreadsheet that has 11000+ rows. I need to move
every other row to a column. With this said, the rows must stay in order
because A1 correspondes with A2, A3 with A4, A5 with A6, etc.

So I have:

name1
address1
name2
address2
name3
address3

I want:

name 1 address1
name 2 address2
name 3 address 3

Are there any quick ways to do this? Thanks.

Here is a macro

Sub Test()
Dim iLastRow As Long
Dim i As Long

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
If (iLastRow \ 2) * 2 < iLastRow Then
iLastRow = iLastRow - 1
End If
For i = cLastRow To 2 Step -2
Cells(i - 1, "B").Value = Cells(i, "A").Value
Rows(i).Delete
Next i

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"ad" wrote in message
...
I am currently working on a spreadsheet that has 11000+ rows. I need to
move
every other row to a column. With this said, the rows must stay in order
because A1 correspondes with A2, A3 with A4, A5 with A6, etc.

So I have:

name1
address1
name2
address2
name3
address3

I want:

name 1 address1
name 2 address2
name 3 address 3

Are there any quick ways to do this? Thanks.

A method I have used ot do this is to enter in B1
=A2
I then copy B1 down to the bottom of the data.
I then copy Column B and paste values over the B column
In C1 I enter 1
in C2 I enter 2
and copy C1 and C2 down tro the bottom of the data making sure it stays 1
and 2
I then select all and select auto filter
In column C I select 2
and delete all of the rows which have a 2 in them.
or you can select all and sort by column C. Again delete all of the rows
with a 2.

If your data is on a page with other data you dont want to disturb, you can
copy the data to a new sheet. and then copy it back when you are done.

There are many more esoteric methods of doing something like this, but if I
only have to do something once in a while, I prefer doing a brute force
method. I have gotten into trouble by using esoteric methods for things they
weren't designed for.
"ad" wrote:
If you have ot do this very often, I would set up a macro to do it.

I am currently working on a spreadsheet that has 11000+ rows. I need to move
every other row to a column. With this said, the rows must stay in order
because A1 correspondes with A2, A3 with A4, A5 with A6, etc.

So I have:

name1
address1
name2
address2
name3
address3

I want:

name 1 address1
name 2 address2
name 3 address 3

Are there any quick ways to do this? Thanks.

thanks bj, that worked perfect!

"bj" wrote:

A method I have used ot do this is to enter in B1
=A2
I then copy B1 down to the bottom of the data.
I then copy Column B and paste values over the B column
In C1 I enter 1
in C2 I enter 2
and copy C1 and C2 down tro the bottom of the data making sure it stays 1
and 2
I then select all and select auto filter
In column C I select 2
and delete all of the rows which have a 2 in them.
or you can select all and sort by column C. Again delete all of the rows
with a 2.

If your data is on a page with other data you dont want to disturb, you can
copy the data to a new sheet. and then copy it back when you are done.

There are many more esoteric methods of doing something like this, but if I
only have to do something once in a while, I prefer doing a brute force
method. I have gotten into trouble by using esoteric methods for things they
weren't designed for.
"ad" wrote:
If you have ot do this very often, I would set up a macro to do it.

I am currently working on a spreadsheet that has 11000+ rows. I need to move
every other row to a column. With this said, the rows must stay in order
because A1 correspondes with A2, A3 with A4, A5 with A6, etc.

So I have:

name1
address1
name2
address2
name3
address3

I want:

name 1 address1
name 2 address2
name 3 address 3

Are there any quick ways to do this? Thanks.