Column wraps in Excel

  • Thread starter Thread starter Sharlene
  • Start date Start date
S

Sharlene

This is my first time asking a question on this board so
I'm seeking Excel experts...

I have more than a thousand entries in a single column
which needs to be printed. To save paper, how do I break
the single column into multiple columns without cutting
and pasting to save paper and to save time?
 
Hi Sharlene!

See:

SNAKECOLS, How to snake columns to use fewer pages
http://www.mvps.org/dmcritchie/excel/snakecol.htm

Several are options available to get round the lack of this feature in
Excel.


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Sharlene

Manualy............

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 100
rows will produce your 10 columns of 100 rows. Any more than 1000 original
rows, you do the math and make alterations.

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

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...............

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