Reformat a large column, to a grid

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is there a simple way (i.e. without writing a macro) to take a very long
column of numbers, and arrange them as a table (or a grid) for printing?

I want to print the contents of a sheet, with one really long column of
numbers but have it appear as many columns to cut down on the massive number
of pages it would take to print the column.
 
Stu said:
Is there a simple way (i.e. without writing a macro) to take a very long
column of numbers, and arrange them as a table (or a grid) for printing?

I want to print the contents of a sheet, with one really long column of
numbers but have it appear as many columns to cut down on the massive
number
of pages it would take to print the column.
Click to expand...

There isn't a simple way to do this.
Have a look here for a discussion and suggestions:
http://www.mvps.org/dmcritchie/excel/snakecol.htm
 
If your data is an column A starting at Cell A1, then the following
formula, entered in Cell B1 and filled across 10 columns and down 50
rows will produce 10 columns of 50 rows. Any more/less than 500 original
rows, you do the math and make alterations.

=INDIRECT("A"&(ROW()+(COLUMN()-2)*50))


The 2 refers to the column of Cell B1; if you're putting the formula in
a different column, use the appropriate number for that column.

Copy>Paste Special(in place) the results then delete the original column A.

VBA Macro to snake the columns top to bottom..1 to 50 down then 51 to 100 etc.


Public Sub SplitToCols()
Dim NUMCOLS As Integer
Dim i As Integer
Dim colsize As Long
On Error GoTo fileerror
NUMCOLS = InputBox("Choose Final Number of Columns")
colsize = Int((ActiveSheet.UsedRange.Rows.Count + _
(NUMCOLS - 1)) / NUMCOLS)
For i = 2 To NUMCOLS
Cells((i - 1) * colsize + 1, 1).Resize(colsize, 1).Copy Cells(1, i)
Next i
Range(Cells(colsize + 1, 1), Cells(Rows.Count, 1)).Clear
fileerror:
End Sub


Gord Dibben Excel MVP
 
Back
Top