Moving Rows

[Ken]

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

 

[Tom Ogilvy]

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.

 

[pikus]

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

 

[Tom Ogilvy]

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

That isn't what was asked for.

 

[pikus]

You're right. Here:

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