Multiple Columns

[seendcleeve]

How can I get Excel to convert one long column of data into
multiple columns for printing. so as not to wast paper
( Without cutting and pasting

 

[Peo Sjoblom]

See

http://www.mvps.org/dmcritchie/excel/snakecol.htm

 

[Paul B]

here is a macro for column A, do not use if you have data in other columns

Sub One_Page_Columns()
'Convert data in column A to columns one-page long
'*******WILL OVERWRITE DATA IN OTHER COLUMNS********
Dim rw%, col%
On Error GoTo e
rw = ExecuteExcel4Macro("INDEX(GET.DOCUMENT(64)," & 1 & ")")
col = 1
Do
Range(Cells(rw, col), Cells(65536, col).End(xlUp)).Resize(, 1).Cut Cells(1,
col + 1)
col = col + 1
If Cells(rw, col).End(xlDown).Row = 65536 Then Exit Do
Loop
e:
On Error GoTo 0
End Sub

--
Paul B
Always backup your data before trying something new
Using Excel 97 & 2000
Please post any response to the newsgroups so others can benefit from it
** remove news from my email address to reply by email **

 

[Gord Dibben]

Manually....

If your data is in 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 10 snaking columns out of 1000 rows. You do the arithmetic
for any other configuration.

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

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

Macro...........

Public Sub SplitToCols()
Dim NUMCOLS As Integer
Dim i As Integer
Dim colsize As Long
On Error GoTo fileerror

NUMCOLS = InputBox("Choose 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 XL2002

 

[Dave Peterson]

If it's a one time shot, I'd just copy the range into MSWord and then format
that for multiple columns.

And print from there.

(If it makes sense, it might be easier to just leave the data in Word, too.)