Might be a long shot - Long Column to Page of Columns...

jimmy2468

New Member
Joined
Oct 10, 2006
Messages
9
This might be tough one, but I figured I'd post it... why not.

I have a long list of data (about 2000 cells - one column A1 - A2000). This data changes, and occasionally I have to print all the data on 8.5/11 paper.
Is there a quick way to format this long list such that the data arranges itself in subsequent columns until it is at the end of the printable area?

That is, if one page can fit A1 to A52 in the printable area,
and the printable area goes from columns A to I in width,
is there a quick way to organise A1 to A2000 so that A1 to A52 appears in A, A53 to A105 appears in column B and so on until it reaches column I, and then starts another print-area page?

Just checking... Thanks!
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,885
Hi jimmy2468

An alternative to what you ask is to use an auxilliary worksheet where you arrange the data in a suitable way for printing.

In this example you have your data in Sheet1 in A1:A2000. You run the code and Sheet2 is cleared and receives the data arranged for printing.

Please try:
Code:
Sub PreparePrint()
Dim lrow As Long, lTargetRow As Long, lTargetCol As Integer
Const MaxCol As Integer = 9, MaxPageRow = 52

Worksheets("Sheet2").UsedRange.Clear
For lrow = 1 To 2000 Step MaxPageRow
    lTargetRow = 1 + MaxPageRow * (lrow \ (MaxPageRow * MaxCol))
    lTargetCol = 1 + (lrow Mod (MaxPageRow * MaxCol)) \ MaxPageRow
    Worksheets("Sheet2").Cells(lTargetRow, lTargetCol).Resize(MaxPageRow).Value = _
        Worksheets("Sheet1").Cells(lrow, 1).Resize(MaxPageRow).Value
Next
End Sub

Hope this helps
PGC
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,885
Hi Again

From PM you'd like 2 other things:

- To be able to specify the Start and End rows to be printed.
- To copy just exactly until the End row

Please try:

Code:
Sub PreparePrint()
Dim lrow As Long, lTargetRow As Long, lTargetCol As Integer, iNRowsPrint As Integer
Const MaxCol As Integer = 9, MaxPageRow As Integer = 52
Const StartRow As Long = 270, EndRow As Long = 1206

Worksheets("Sheet2").UsedRange.Clear
For lrow = StartRow To EndRow Step MaxPageRow
    lTargetRow = 1 + MaxPageRow * ((lrow - StartRow) \ (MaxPageRow * MaxCol))
    lTargetCol = 1 + ((lrow - StartRow) Mod (MaxPageRow * MaxCol)) \ MaxPageRow
    iNRowsPrint = IIf(MaxPageRow <= EndRow - lrow, MaxPageRow, 1 + EndRow - lrow)
    Worksheets("Sheet2").Cells(lTargetRow, lTargetCol).Resize(iNRowsPrint).Value = _
        Worksheets("Sheet1").Cells(lrow, 1).Resize(iNRowsPrint).Value
Next
End Sub

This is of course just the core of the solution. You can add things like input boxes to ask the limit rows, to choose other column other than A, to choose other worksheets as source or target, etc.

Hope this is what you want
PGC
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,885
I'm glad it helped.

This is one of those I'll keep. You never know when you need it.

Cheers
PGC
 

Forum statistics

Threads
1,144,161
Messages
5,722,838
Members
422,460
Latest member
VBA_Noob01

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top