how do I rearrange a column into a series of columns?

[Guest]

some one sent ma large file with seven recurring fields all in one single
column. Is there some macro that will move each of seven rows to a separate
column and then restart the process with each seventh line to the end of the
file. There are too many items to do this manually? Each field item recurs
every seven lines.

 

[Guest]

Hi,

This assumes Column A contains your list, starting row 1, and
tranposes data to Columns B to H, starting Row 1. When completed you can
delete Column A.

Sub TranposeRows()
Dim inrow As Long, lastrow As Long
Application.ScreenUpdating = False
With Worksheets("sheet1")
lastrow = .Cells(Rows.Count, "A").End(xlUp).Rows
Set outrng = .Range("B1")
For inrow = 1 To lastrow Step 7
.Range("A" & inrow).Resize(7, 1).Copy
outrng.Resize(1, 7).PasteSpecial Paste:=xlPasteAll,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Set outrng = outrng.Offset(1, 0)
Next inrow
End With
Application.ScreenUpdating = True
End Sub


HTH

 

[Alan Beban]

some one sent ma large file with seven recurring fields all in one single
column. Is there some macro that will move each of seven rows to a separate
column and then restart the process with each seventh line to the end of the
file. There are too many items to do this manually? Each field item recurs
every seven lines.

You will be limited by the maximum number of columns in the
spreadsheet--255.

One way: If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook, array
enter into a 7-row range of enough columns to accommodate the output

=ArrayReshape(dataRange,7,ROWS(dataRange)/7,TRUE)

Alan Beban

 

[Doug Glancy]

R Morahn,

This took me forever to figure out! I think it works though. Assuming your
data is in column A, this puts the results in column B through H:

Sub test()
Dim from_cells_count As Long
Dim i As Long
Dim row_num As Long
Dim col_num As Long

from_cells_count = (Range("A" & Rows.Count).End(xlUp).Row)
For i = 1 To from_cells_count
If Int(i / 7) <> i / 7 Then
row_num = Int(i / 7) + 1
col_num = (i Mod 7) + 1
Else
row_num = i / 7
col_num = 8
End If
Cells(row_num, col_num) = Range("A" & i)
Next i
End Sub

hth,

Doug

 

[Guest]

You will be limited by the maximum number of columns in the
spreadsheet--255.

One way: If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook, array
enter into a 7-row range of enough columns to accommodate the output

=ArrayReshape(dataRange,7,ROWS(dataRange)/7,TRUE)

Alan Beban

Thanks.

I need a little more help. When I past the command, do I enter it as html or
unicode? I don't know how to name the data range of 4276 lines in column a1.

Can you help me with that?

 

[Guest]

Hi,

This assumes Column A contains your list, starting row 1, and
tranposes data to Columns B to H, starting Row 1. When completed you can
delete Column A.

Sub TranposeRows()
Dim inrow As Long, lastrow As Long
Application.ScreenUpdating = False
With Worksheets("sheet1")
lastrow = .Cells(Rows.Count, "A").End(xlUp).Rows
Set outrng = .Range("B1")
For inrow = 1 To lastrow Step 7
.Range("A" & inrow).Resize(7, 1).Copy
outrng.Resize(1, 7).PasteSpecial Paste:=xlPasteAll,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Set outrng = outrng.Offset(1, 0)
Next inrow
End With
Application.ScreenUpdating = True
End Sub


HTH

Thanks.

Do i just copy and place this routine in th etop column next to my long
column of data? When I do a simple copy and paste, it spreads over a number
of rows. Is there some way to get it all into one cell? While it then
calculate and move everything?

Thnaks for your help.

 

[Guest]

R Morahn,

This took me forever to figure out! I think it works though. Assuming your
data is in column A, this puts the results in column B through H:

Sub test()
Dim from_cells_count As Long
Dim i As Long
Dim row_num As Long
Dim col_num As Long

from_cells_count = (Range("A" & Rows.Count).End(xlUp).Row)
For i = 1 To from_cells_count
If Int(i / 7) <> i / 7 Then
row_num = Int(i / 7) + 1
col_num = (i Mod 7) + 1
Else
row_num = i / 7
col_num = 8
End If
Cells(row_num, col_num) = Range("A" & i)
Next i
End Sub

hth,

Doug

doug--

i don't know how to paste the routine. when I copy and paste, each line goes
into a different row, and so it doesn't execute. Do i have to do anyhting to
the cell where I'm pasting the routine?

Thanks,

Rich

 

[Doug Glancy]

R Morahn,

This is Visual Basic code. You don't paste it into the worksheet. You need
to open the Visual Basic Editor (VBE), by pressing Alt-F11, insert a module
and paste the code into that. You can then add a button from the Forms
toolbar and assign this macro to it. Or you can run it from inside the VBE
by putting your cursor somewhere inside the macro and pressing F5.

If you are planning on doing more than this, you probably need to read up on
VBA and the VBE.

hth,

Doug

 

[Guest]

R Morahn,

This is Visual Basic code. You don't paste it into the worksheet. You need
to open the Visual Basic Editor (VBE), by pressing Alt-F11, insert a module
and paste the code into that. You can then add a button from the Forms
toolbar and assign this macro to it. Or you can run it from inside the VBE
by putting your cursor somewhere inside the macro and pressing F5.

If you are planning on doing more than this, you probably need to read up on
VBA and the VBE.

hth,

Doug




Thnaks Doug

 

[Tom Ogilvy]

Alan appears to think you want 7 rows and 611 columns for your result.

Maybe you should clear that up first.

In any event, go to the name box in the upper left corner of the formatting
toolbar (it has the address of the active cell in it) and put in

A1:A4276<cr>
this will select A1:A4267

now go to the same box and enter

dataRange<cr>

you can then go into Insert=>Name=>Define and you should see dataRange
listed. You can select it and see it is defined as

=Sheet1!$A$1:$A$4267

You could also create a name here by typing in the refers to (using absolute
references), then going to the Name area in the dialog and entering a name,
then clicking Add.

--
Regards,
Tom Ogilvy