How can I move alternate rows in an Excel table up one and over one?

For example I want:

a
b
c
d
e
f

to become:

ab
cd
ef

Thanks.

Ken

assume a is in A1

in B1 put in

=if(mod(row(),2)=1,A2,na())
then drag fill down the column

Now select the column, and do Edit=Copy, then immediately do Edit=Paste
Special and select Values

The (column still selected) do Edit=Goto=Special and select constants and
(uncheck all but errors) errors, then do delete and select entirerow.

--
Regards,
Tom Ogilvy

"Ken" wrote in message
om...
How can I move alternate rows in an Excel table up one and over one?

For example I want:

a
b
c
d
e
f

to become:

ab
cd
ef

Thanks.

Ken

colNum = 1
a = 1
Do
x = x + 1
Loop Until Cells(x + 1, colNum).Value = ""
y = (x / 2) + 1
For z = y To x
Worksheets(1).Cells(z, colNum).Copy
Worksheets(1).Cells(a, colNum + 1).Insert
Worksheets(1).Cells(z, colNum).Value = ""
a = a + 1
Next z

That should work fine. - Pikus


---
Message posted from http://www.ExcelForum.com/

that takes the lower half of the list and puts it in the next column.

That isn't what was asked for.

--
Regards,
Tom Ogilvy

"pikus" wrote in message
...
colNum = 1
a = 1
Do
x = x + 1
Loop Until Cells(x + 1, colNum).Value = ""
y = (x / 2) + 1
For z = y To x
Worksheets(1).Cells(z, colNum).Copy
Worksheets(1).Cells(a, colNum + 1).Insert
Worksheets(1).Cells(z, colNum).Value = ""
a = a + 1
Next z

That should work fine. - Pikus


---
Message posted from http://www.ExcelForum.com/

You're right. He

colNum = 1
a = 1

Do
x = x + 1
Loop Until Cells(x + 1, colNum).Value = ""

For z = a To x
If z Mod 2 = 0 Then
Worksheets(1).Cells(z, colNum).Copy
Worksheets(1).Cells(z - 1, colNum + 1).Insert
End If
Next z

For z = a To x
If Worksheets(1).Cells(z, colNum + 1).Value = "" Then
Worksheets(1).Rows(z).Delete
End If
Next z

Thanks for the clarification, Yo! - Pikus


---
Message posted from http://www.ExcelForum.com/