Moving cells

[SeanK137]

All, tried to search around but didn't see anything similar to my issue.

I have an excel sheet that has all of its information in grouped in column a that should be organized in a different manner. Every 6 cells contains a group of information, which I would like to separate. For example, there is information in a1:a6. A1 should stay in a1, A2 --> B1, A3 --> C1, A4 --> D1, A5 --> E1 and A6 --> F1. I need the macro to keep going through the rest of the sheet placing the information in a2, b2, c2, etc where appropriate until all of the information is dispersed. Any suggestions?

 

[texasalynn]

select the data in column A (not the whole column, only those with data) do a copy / paste special, click transpose

 

[onlyadrafter]

Hello,

does this work as expected?

Sub TRANSPOSE()
    For MY_ROWS = 1 To Range("A" & Rows.Count).End(xlUp).Row Step 6
        Range("A" & MY_ROWS & ":A" & MY_ROWS + 5).Copy
        Range("B65536").End(xlUp).Offset(1, 0).Select
        Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, TRANSPOSE:=True
    Next MY_ROWS
    Columns(1).Delete
    Rows(1).Delete
End Sub

 

[SeanK137]

Very Cool! I had no idea there was a function for that.

How would I write a macro that takes each group of 6 cells and transposes them?

 

[lenze]

Very Cool! I had no idea there was a function for that.

How would I write a macro that takes each group of 6 cells and transposes them?

I believe the above code by onlyadrafter does that (Not tested), but this also will

Sub Transpose()
Dim i, j, k As Integer
j = 6
k = 1
For i = 1 To 1000 Step 6
If Cells(i, 1) = "" Then Exit Sub
Range(Cells(i, 1), Cells(j, 1)).Copy
Cells(k, 2).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
j = j + 6
k = k + 1
Next i
End Sub

lenze

 

[SeanK137]

It definitely does, we must have posted at the same time. Thanks to all for your help!