In Excel, how can I print a long list on 1 page in multiple colum.

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

Guest

I have a long (700 rows) list in an Excel worksheeet. I would like to print
the whole list on one page by making the font smaller and by printing it on
multiple columns on the page. How can I set this up?

Thanks
 
Chuck

What sort of orientation would you like?

Manually............

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 70
rows will produce 10 columns of 70 rows. Any more/less than 700 original
rows, you do the math and make alterations.

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

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 70 down then 71 to 140
down

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

VBA macro to snake the columns side to side...1 to 10 left to right then 11 to
20 left to right

Sub ColtoRows()
Dim rng As Range
Dim i As Long
Dim j As Long
Dim nocols As Long
Set rng = Cells(Rows.Count, 1).End(xlUp)
j = 1
On Error Resume Next
nocols = InputBox("Enter Number of Columns Desired")

For i = 1 To rng.Row Step nocols
Cells(j, "A").Resize(1, nocols).Value = _
Application.Transpose(Cells(i, "A").Resize(nocols, 1))
j = j + 1
Next
Range(Cells(j, "A"), Cells(rng.Row, "A")).ClearContents
End Sub


Gord Dibben Excel MVP
 
Chuck

Forgot about this part..

When you get the columns as you like, go into Page Setup and set for "Print 1
page wide by 1 page tall"


Gord
 
Back
Top