Fitting 2 Columns / 20,000 cells on fewer pages as possible? (Printout)

JumboCactuar

Well-known Member
Joined
Nov 16, 2016
Messages
785
Office Version
  1. 365
Platform
  1. Windows
Hi,
looking for some ideas on best way to achieve this

I have data in:
Columns A + B (Header A1 and B1) down to around row 20000 (max characters per cell is 15)

and i need to compress the data so its printable with row 1 repeating.

I thought of:
viewing in page break preview
copying everying under the line into column C/D and repeating.

It has to stay in order so the last cell on page 1 follows to next cell on page2

Any way to automate this with VBA? Or any better way of achieving the same

Appreciate any help
 
Last edited:

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
copy paste your two columns into word (this may take a while)
select your top row, go to the table tools tab>layout>data and select repeat header rows
go to the layout tab (not the layout tab under table tools, the other layout tab)>page setup, select columns and increase the number of columns per page

hope that worked
 
Upvote 0
copy paste your two columns into word (this may take a while)
select your top row, go to the table tools tab>layout>data and select repeat header rows
go to the layout tab (not the layout tab under table tools, the other layout tab)>page setup, select columns and increase the number of columns per page

hope that worked

Thanks I will try this :)
 
Upvote 0
so difficult to achieve in Word due to constant crashing / not responding

managed to paste it in and got 5 columns per page, but resizing and making sure each row is same size is tricky
will have another attempt tomorrow

Probably better working with a small dataset till I can figure the right formatting
 
Last edited:
Upvote 0
try this in excel

Code:
Sub pagebreaktest1()
Dim pbrow As Long 'rows in a page
Dim i As Long
Dim pgtot As Integer 'total number of pages
Dim pgcount As Integer
Dim colnum As Integer

For Each Row In Rows
If Row.PageBreak = xlAutomatic Then
pbrow = Row.Row - 2
Exit For
End If
Next Row

pgtot = ActiveCell.SpecialCells(xlCellTypeLastCell).Row \ pbrow + 1

For pgcount = 2 To pgtot
Range("A" & 50 + 49 * (pgcount - 2) + 1, "B" & 50 + 49 * (pgcount - 1)).Copy Destination:=Cells(2, (pgcount - 1) * 2 + 1)
Range("A1:B1").Copy Destination:=Cells(1, (pgcount - 1) * 2 + 1)
Next pgcount

ActiveSheet.PageSetup.PrintArea = ""
ActiveSheet.PageSetup.PrintArea = Range(Rows(1), Rows(pbrow + 1)).Address


ActiveSheet.ResetAllPageBreaks
'********
colcount = 4 'customise number of desired "columns" per page
'********
For i = colcount * 2 To ActiveCell.SpecialCells(xlCellTypeLastCell).Column Step colcount * 2
ActiveSheet.VPageBreaks.Add Before:=Columns(i + 1)
Next i
End Sub
 
Upvote 0
wait sorry try this

Code:
Sub pagebreaktest1()
Dim pbrow As Long 'rows in a page
Dim i As Long
Dim pgtot As Integer 'total number of pages
Dim pgcount As Integer
Dim colnum As Integer

ActiveSheet.PageSetup.PrintArea = ""
ActiveSheet.ResetAllPageBreaks

For Each Row In Rows
If Row.PageBreak = xlAutomatic Then
pbrow = Row.Row - 2
Exit For
End If
Next Row

pgtot = ActiveCell.SpecialCells(xlCellTypeLastCell).Row \ pbrow + 1

For pgcount = 2 To pgtot
Range("A" & pbrow + 1 + pbrow * (pgcount - 2) + 1, "B" & pbrow + 1 + pbrow * (pgcount - 1)).Copy Destination:=Cells(2, (pgcount - 1) * 2 + 1)
Range("A1:B1").Copy Destination:=Cells(1, (pgcount - 1) * 2 + 1)
Next pgcount

ActiveSheet.PageSetup.PrintArea = Range(Rows(1), Rows(pbrow + 1)).Address

'********
colcount = 4 'customise number of desired "columns" per page
'********
For i = colcount * 2 To ActiveCell.SpecialCells(xlCellTypeLastCell).Column Step colcount * 2
ActiveSheet.VPageBreaks.Add Before:=Columns(i + 1)
Next i
End Sub
 
Upvote 0
wait sorry try this

Code:
Sub pagebreaktest1()
Dim pbrow As Long 'rows in a page
Dim i As Long
Dim pgtot As Integer 'total number of pages
Dim pgcount As Integer
Dim colnum As Integer

ActiveSheet.PageSetup.PrintArea = ""
ActiveSheet.ResetAllPageBreaks

For Each Row In Rows
If Row.PageBreak = xlAutomatic Then
pbrow = Row.Row - 2
Exit For
End If
Next Row

pgtot = ActiveCell.SpecialCells(xlCellTypeLastCell).Row \ pbrow + 1

For pgcount = 2 To pgtot
Range("A" & pbrow + 1 + pbrow * (pgcount - 2) + 1, "B" & pbrow + 1 + pbrow * (pgcount - 1)).Copy Destination:=Cells(2, (pgcount - 1) * 2 + 1)
Range("A1:B1").Copy Destination:=Cells(1, (pgcount - 1) * 2 + 1)
Next pgcount

ActiveSheet.PageSetup.PrintArea = Range(Rows(1), Rows(pbrow + 1)).Address

'********
colcount = 4 'customise number of desired "columns" per page
'********
For i = colcount * 2 To ActiveCell.SpecialCells(xlCellTypeLastCell).Column Step colcount * 2
ActiveSheet.VPageBreaks.Add Before:=Columns(i + 1)
Next i
End Sub

Thanks :) it's almost spot on

My original page cuts off to page 2 after row 92
But whatever size/font/column width etc.. after running this code it sets page break at row 51

Current page settings:
Font: Calibri 12
Column width:16
Row Height: 16
Page setup: shrink to 55%
0 top/bottom margins

These give me 92 rows per page (default orientation)
 
Upvote 0
maybe try set the font and other formatting before running the code
other than that im not sure what else might work
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,306
Members
448,564
Latest member
ED38

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
Back
Top