macro help

[nofootprints]

i have a spreadsheet with all information in column A
The information is in cells as follows

A1
A3
A5
down to 16800
every second row is blank

I need to move the information in the cells as follows

A1 static
A3 to B1
A5 to C1

Then it loops

A7 to A2
A9 to B2
A11 to C2

Loops again

A13 to A 3
A15 to B 3
A 17 to C3

and so on.

 

[repairman615]

Hello,

Welcome to the board!

Try on a copy to see if this does as you expected:

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> MoveCells()<br><br><SPAN style="color:#007F00">''' Press Alt + F11</SPAN><br><SPAN style="color:#007F00">'''Alt - I - M</SPAN><br><SPAN style="color:#007F00">'''Paste in the below</SPAN><br><br><SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>, n <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>, x <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> cell <SPAN style="color:#00007F">As</SPAN> Range<br><br>i = 1<br>n = 0<br>x = 1<br><br><SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> cell <SPAN style="color:#00007F">In</SPAN> Range("A2", Range("A" & Rows.Count).End(xlUp))<br>    <SPAN style="color:#00007F">If</SPAN> i = 1 <SPAN style="color:#00007F">Then</SPAN><br>        i = i + 1<br>    <SPAN style="color:#00007F">Else</SPAN><br>        Range("A1").Offset(n, x).Value = cell.Value<br>        i = 1<br>        <br>        <SPAN style="color:#00007F">If</SPAN> x = 2 <SPAN style="color:#00007F">Then</SPAN><br>            n = n + 1<br>            x = 0<br>        <SPAN style="color:#00007F">Else</SPAN><br>            x = x + 1<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <br>        <SPAN style="color:#00007F">If</SPAN> i = 2 <SPAN style="color:#00007F">Then</SPAN> i = 1<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">Next</SPAN> cell<br>    <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[repairman615]

Hello,

The above I did forget to clear the contents of the original cells. Here is an update:

Does this do as you would like?

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> MoveCells()<br><br><SPAN style="color:#007F00">''' Press Alt + F11</SPAN><br><SPAN style="color:#007F00">'''Alt - I - M</SPAN><br><SPAN style="color:#007F00">'''Paste in the below</SPAN><br><br><SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>, n <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>, x <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> cell <SPAN style="color:#00007F">As</SPAN> Range<br><br>i = 1<br>n = 0<br>x = 1<br><br><SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> cell <SPAN style="color:#00007F">In</SPAN> Range("A2", Range("A" & Rows.Count).End(xlUp))<br>    <SPAN style="color:#00007F">If</SPAN> i = 1 <SPAN style="color:#00007F">Then</SPAN><br>        i = i + 1<br>    <SPAN style="color:#00007F">Else</SPAN><br>        Range("A1").Offset(n, x).Value = cell.Value<br>        cell.ClearContents <SPAN style="color:#007F00">'''Clears the Original Data, Thanks Marcelo.</SPAN><br>        i = 1<br>        <br>        <SPAN style="color:#00007F">If</SPAN> x = 2 <SPAN style="color:#00007F">Then</SPAN><br>            n = n + 1<br>            x = 0<br>        <SPAN style="color:#00007F">Else</SPAN><br>            x = x + 1<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <br>        <SPAN style="color:#00007F">If</SPAN> i = 2 <SPAN style="color:#00007F">Then</SPAN> i = 1<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">Next</SPAN> cell<br>    <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br></FONT>

 

[nofootprints]

thanks my friend. yr input much appreciated.