Solved

Print wrapped columns in Excel 2010

Posted on 2011-03-05
7
1,325 Views
Last Modified: 2012-05-11
Hi Experts,

How can I get "Excel 2010" to print wrapped columns?

I have two narrow columns within a worksheet.
I would like to wrap(snake)  the columns so that a printed page is filled before going to print the next sheet.
So rows 1 through 60 are in the first two columns page 1,  the next 60 rows are in the 3rd & 4th columns (again page 1), etc.

Thanks
0
Comment
Question by:AndyPandy
  • 3
  • 3
7 Comments
 
LVL 24

Expert Comment

by:jimyX
ID: 35045241
You can format the cells:
Select your cells -> right click -> format cells -> from the Alignment tab check the Wrap text then OK.
0
 
LVL 24

Expert Comment

by:jimyX
ID: 35045258
Another way is from Home -> Alignment -> Wrap Text.
0
 
LVL 24

Expert Comment

by:jimyX
ID: 35045262
And to adjust the columns visibility you may adjust the column's width and the row's height until you get your desired form to print.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 42

Expert Comment

by:dlmille
ID: 35045747
I did a solution on somthing like this (there were two solutions, and mine wasn't picked.  feel free to use the first, but I created a macro that broke the three columns up into page sizes and formatted a page from those columns that could be printed).

http://www.experts-exchange.com/Q_26732871.html

Let me know if this helps and if you want to further persue, I"m happy to help!

Cheers,

Dave
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35045827

If you want to keep it all in Excel, the attached "wraps" to 60 rows, and operates on the first 3 columns.  See attached:

The "create word" document tip in the link I sent in the post (above) is a good one, but it is a bit "manual", though a macro COULD be written to handle.

See the second attachment, rewritten to handle the first three Columns in Excel and outputs to the printed page.

If you'd like either modified to suit, or you can take it from here, please let me know!

Dave

its-quick-by-hand-r2.xlsx
0
 
LVL 42

Accepted Solution

by:
dlmille earned 500 total points
ID: 35045884
The following code assumes a long list of two columns.  It creates an "output sheet" dynamically after prompting you for maximum rows per page of print, and whether you have headers or not.

The App then generates a set of columns, 60 rows by 2 columns, then shifts to the right and regenerates again and again until the end of the data.

Hope this helps.  The 60 rows and 2 columns are defaults, and I've added code to prompt you for those, in case your needs change.

Dave
Sub GenerateOutputToPrint()
Dim outSheet As Worksheet
Dim inSheet As Worksheet
Dim outCursor As Range
Dim lRow As Long, lastRow As Long
Dim maxRows As Long, repeatAtTop As Boolean
Dim numColumns As Long

Application.ScreenUpdating = False

    maxRows = InputBox("Enter Max Rows Per Page of Output", "E.g., 60", 60)
    numColumns = InputBox("Enter # Columns in your dataset for printing", "E.g., 2", 2)
    repeatAtTop = MsgBox("Do your columns have headers?", vbYesNo)
    
    
    'assumes two columns of data, per original question asked (dlmille E-E on 3/6/2011)
    
        lastRow = Cells.Find(What:="*", After:=[A1], _
                SearchOrder:=xlByRows, _
                SearchDirection:=xlPrevious).Row

        Set inSheet = ActiveSheet
        Set outSheet = Worksheets.Add ' this is where the output will go
        Set outCursor = outSheet.Range("A1")
        
        
        For lRow = 1 To lastRow
        
            outCursor.Resize(1, numColumns).Value = Range(inSheet.Cells(lRow, 1), inSheet.Cells(lRow, numColumns)).Value 'copy the first two columns to output page

            If (lRow - 1) Mod maxRows = 0 And lRow > 1 Then 'every 60 rows, generate new set of columns - so shift outCursor to the right, and back up to the top!
                Set outCursor = outCursor.Offset(0, numColumns).End(xlUp)
                
                If repeatAtTop Then 'get headers and repeat them
                    outCursor.Resize(1, numColumns).Value = Range(inSheet.Cells(1, 1), inSheet.Cells(1, numColumns)).Value 'copy the first two column headers to new columns
                    Set outCursor = outCursor.Offset(1, 0) 'ready for next row of input
                End If
            Else
                Set outCursor = outCursor.Offset(1, 0) 'ready for next row of input
            End If
        Next lRow
                    
Application.ScreenUpdating = True
End Sub

Open in new window

WrapColumnsForPrint-r1.xlsm
1
 

Author Comment

by:AndyPandy
ID: 35061765
Thanks dmille, your BA worked well!
I am surprised there is not a native function within excel to do this.
Excel-columns.JPG
0

Featured Post

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question