Need single column/row of numbers to print down then across pages in order

  • Thread starter Thread starter Curt
  • Start date Start date
C

Curt

I have a very long list of phone numbers that occupies
a1,a2, so on. approx. 45,000 records. I need to be able
to print hard copies of these numbers so that it stays in
order from page to page but is not just one column. (as
to cut the # of pages from 300 to 50 or lesss) In other
words, start in the top left corner of page through to
bottom, next row, and so on. I thought there would be a
way to set this up in printing options but to this point
haven't figured it out. I have the same list (1 row
again) also in Access if it would be easier. Would VERY
much appreciate any clues. Thanks.
 
Curt

Either of these methods will "snake" the original column. If you want some
other format, post back.

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 4500
rows will produce your 10 columns of 4500 rows. If you want more or less than
10 columns, you do the math and make alterations.

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

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
 
Hi again. Appreciate your help. That almost does it but
I wasn't too clear on exactly what I was looking for. I
need the hardcopy to be in numerical order page by page.
In other words, I can't have 4500 records per row. (Need
to be able to reference numbers in order on hard copy)
Only 60 max will fit per page. Whew. So basically, if it
were just numbers in order starting at 1 I need 1-60 in
column A, then 61-120 in column B until there are 10
rows.. And from that point continue in order for each
consecutive page that will be printed out. Only way I've
found to do this is a painfully long slow process of
copy/paste. Sorry if I'm being a pain & I do appreciate
any help. Thank you.... Curt
 
Curt

Try this one. It is hard-coded to give you 10 columns in the format you want
with a blank row between each set of 60 rows.

I'm sure one of the brighter lights could revise to loop rather than hard-code
the cut and destination ranges.

Sub Move_Sets()
Dim iSource As Long
Dim iTarget As Long

iSource = 1
iTarget = 1

Do
Cells(iSource, "A").Resize(60, 1). _
Cut Destination:=Cells(iTarget, "A")
Cells(iSource + 60, "A").Resize(60, 1) _
.Cut Destination:=Cells(iTarget, "B")
Cells(iSource + 120, "A").Resize(60, 1) _
.Cut Destination:=Cells(iTarget, "C")
Cells(iSource + 180, "A").Resize(60, 1) _
.Cut Destination:=Cells(iTarget, "D")
Cells(iSource + 240, "A").Resize(60, 1) _
.Cut Destination:=Cells(iTarget, "E")
Cells(iSource + 300, "A").Resize(60, 1) _
.Cut Destination:=Cells(iTarget, "F")
Cells(iSource + 360, "A").Resize(60, 1) _
.Cut Destination:=Cells(iTarget, "G")
Cells(iSource + 420, "A").Resize(60, 1) _
.Cut Destination:=Cells(iTarget, "H")
Cells(iSource + 480, "A").Resize(60, 1) _
.Cut Destination:=Cells(iTarget, "I")
Cells(iSource + 540, "A").Resize(60, 1) _
.Cut Destination:=Cells(iTarget, "J")

iSource = iSource + 600
iTarget = iTarget + 61
Loop Until IsEmpty(Cells(iSource, "A").Value)

End Sub

Gord
 
Back
Top