Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to "merge" multiple colums to reduce page count
Sorry for the cryptic subject - wasn't sure how to explain.
I have a spreadsheet comprising (3) columns and 41,000+ lines. I need to know if there is a easy way to reduce the page count vs cutting and pasting additional columns to the right. Any thoughts would be appreciated. Jon |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to "merge" multiple colums to reduce page count
So you want to print let say 9 columns to a page. Assuming that to be correct
then there is nothing as direct as you would want. I would be inclined to on a seperate tab just use formulas to transfer the data over. in A1 =Sheet1!A1 in B1 =Sheet1!B1 in C1 =Sheet1!C1 in D1 =Sheet1!A14000 in E1 =Sheet1!B14000 in F1 =Sheet1!C14000 in G1 =Sheet1!A28000 in H1 =Sheet1!B28000 in I1 =Sheet1!C28000 and drag down. If this is a spreadsheet that you use a lot these formulas will cause your spreadsheet to slow down as you will reach the calculation limit of xl and any calculation will cuase a full workbook recalc. You might want to delete the formulas after you print or switch calculation to manual. -- HTH... Jim Thomlinson "jmcclain" wrote: Sorry for the cryptic subject - wasn't sure how to explain. I have a spreadsheet comprising (3) columns and 41,000+ lines. I need to know if there is a easy way to reduce the page count vs cutting and pasting additional columns to the right. Any thoughts would be appreciated. Jon |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to "merge" multiple colums to reduce page count
Jim,
Can you explain a bit more? Do i need to create a new tab and enter the formulas just as you noted? It's a bit out of my comfort level. I added the first formula, and I get a dialog box for "update values? Can you elaborate? "Jim Thomlinson" wrote: So you want to print let say 9 columns to a page. Assuming that to be correct then there is nothing as direct as you would want. I would be inclined to on a seperate tab just use formulas to transfer the data over. in A1 =Sheet1!A1 in B1 =Sheet1!B1 in C1 =Sheet1!C1 in D1 =Sheet1!A14000 in E1 =Sheet1!B14000 in F1 =Sheet1!C14000 in G1 =Sheet1!A28000 in H1 =Sheet1!B28000 in I1 =Sheet1!C28000 and drag down. If this is a spreadsheet that you use a lot these formulas will cause your spreadsheet to slow down as you will reach the calculation limit of xl and any calculation will cuase a full workbook recalc. You might want to delete the formulas after you print or switch calculation to manual. -- HTH... Jim Thomlinson "jmcclain" wrote: Sorry for the cryptic subject - wasn't sure how to explain. I have a spreadsheet comprising (3) columns and 41,000+ lines. I need to know if there is a easy way to reduce the page count vs cutting and pasting additional columns to the right. Any thoughts would be appreciated. Jon |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to "merge" multiple colums to reduce page count
Yes you would want to create a new worksheet and add the formula to that new
worksheet. -- HTH... Jim Thomlinson "jmcclain" wrote: Jim, Can you explain a bit more? Do i need to create a new tab and enter the formulas just as you noted? It's a bit out of my comfort level. I added the first formula, and I get a dialog box for "update values? Can you elaborate? "Jim Thomlinson" wrote: So you want to print let say 9 columns to a page. Assuming that to be correct then there is nothing as direct as you would want. I would be inclined to on a seperate tab just use formulas to transfer the data over. in A1 =Sheet1!A1 in B1 =Sheet1!B1 in C1 =Sheet1!C1 in D1 =Sheet1!A14000 in E1 =Sheet1!B14000 in F1 =Sheet1!C14000 in G1 =Sheet1!A28000 in H1 =Sheet1!B28000 in I1 =Sheet1!C28000 and drag down. If this is a spreadsheet that you use a lot these formulas will cause your spreadsheet to slow down as you will reach the calculation limit of xl and any calculation will cuase a full workbook recalc. You might want to delete the formulas after you print or switch calculation to manual. -- HTH... Jim Thomlinson "jmcclain" wrote: Sorry for the cryptic subject - wasn't sure how to explain. I have a spreadsheet comprising (3) columns and 41,000+ lines. I need to know if there is a easy way to reduce the page count vs cutting and pasting additional columns to the right. Any thoughts would be appreciated. Jon |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to "merge" multiple colums to reduce page count
Public Sub Snake3to9()
Dim myRange As Range Dim colsize As Long Dim maxrow As Long Const numgroup As Integer = 3 Const NumCols As Integer = 9 On Error GoTo fileerror colsize = Int((ActiveSheet.UsedRange.Rows.Count + _ ((NumCols - 1)) / NumCols)) / numgroup MsgBox "Number of Rows to Move is: " & colsize Range("A1").Select With ActiveCell.Parent.UsedRange maxrow = .Cells(.Cells.Count).Row + 1 End With ActiveCell.Parent.Cells(maxrow, ActiveCell.Column) _ .End(xlUp).Offset(1, 0).Select Set myRange = Range(ActiveCell.Address & ":" _ & ActiveCell.Offset(-colsize, (numgroup - 1)).Address) myRange.Cut Destination:=ActiveSheet.Range("A1").Offset(0, _ (NumCols - numgroup)) Range("A1").Select Cells.End(xlDown).Offset(1, 0).Select Set NextRange = Range(ActiveCell.Address & ":" _ & ActiveCell.Offset(-colsize, (numgroup - 1)).Address) NextRange.Cut Destination:=ActiveSheet.Range("A1").Offset(0, _ (NumCols / numgroup)) Application.CutCopyMode = False Range("A1").Select fileerror: End Sub Gord Dibben MS Excel MVP On Fri, 19 Feb 2010 07:13:01 -0800, jmcclain wrote: Sorry for the cryptic subject - wasn't sure how to explain. I have a spreadsheet comprising (3) columns and 41,000+ lines. I need to know if there is a easy way to reduce the page count vs cutting and pasting additional columns to the right. Any thoughts would be appreciated. Jon |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Create Pivot Table Data with Column "Sum" rather than "count" defa | Excel Discussion (Misc queries) | |||
is it possible to "merge" multiple worksheets into one worksheet? | Excel Discussion (Misc queries) | |||
Multiple "other" delimiters? Easy multiple cell merge? | Excel Discussion (Misc queries) | |||
How Might I "Mail Merge" Data For Multiple .txt File Output? | Excel Discussion (Misc queries) | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel |