Loading
Excel.Tips.Net ExcelTips (Menu Interface)

Printing a Single Column in Multiple Columns

Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Printing a Single Column in Multiple Columns.

Sometimes the data you collect in a worksheet fits very nicely into a single column. For instance, you may have a list of names, and they all are contained in column A of your worksheet. When you choose to print the worksheet, it can consume quite a few pages, all of them nearly blank as the left side of each page contains a name, and the right side contains white space.

In this type of instance, it would be nice to print the single column as if it were multiple columns. That way you could use more of each printed page and fewer overall pages for your print job. Unfortunately Excel contains no intrinsic command or print setting that allows you to automatically reformat your data so it prints better. There are workarounds, however.

One workaround that is often overlooked is just copying the single-column list to a blank Word document. If you paste it there as plain text, you can format each page for multiple columns and actually print the information.

If you would rather not involve Word, you can cut and paste information from the first column into other columns to give the desired number of printing columns. This, of course, should be done in a new worksheet or workbook, so that the original data remains undisturbed. As an example, if you have 200 names in your original list, you can cut 40 names at a time from the list and paste them into columns A through E of a new worksheet. Printing this worksheet requires less pages than printing the original single-column worksheet.

Of course, if you have to do this cut-and-paste often, the chore can quickly become tiresome. In this instance, you can use a macro that does the exact same thing: It slices and dices the original list and pastes it into a number of columns on a new workbook.

Sub SingleToMultiColumn()
    Dim rng As Range
    Dim iCols As Integer
    Dim lRows As Long
    Dim iCol As Integer
    Dim lRow As Long
    Dim lRowSource As Long
    Dim x As Long
    Dim wks As Worksheet

    Set rng = Application.InputBox _
      (prompt:="Select the range to convert", _
      Type:=8)
    iCols = InputBox("How many columns do you want?")
    lRowSource = rng.Rows.Count
    lRows = lRowSource / iCols
    If lRows * iCols <> lRowSource Then lRows = lRows + 1

    Set wks = Worksheets.Add
    lRow = 1
    x = 1
    For iCol = 1 To iCols
        Do While x <= lRows And lRow <= lRowSource
            Cells(x, iCol) = rng.Cells(lRow, 1)
            x = x + 1
            lRow = lRow + 1
        Loop
        x = 1
    Next
End Sub

When you run this macro, you are asked to select the range you want to convert, and then you are asked to specify the number of columns you want it to be reformatted as. It creates a new worksheet in the current workbook and copies information from the original into as many columns as you specified.

For additional resources to solve this problem, refer to the following Web sites:

http://www.ozgrid.com/VBA/MiscVBA.htm#Print
http://www.mvps.org/dmcritchie/excel/snakecol.htm

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2219) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Printing a Single Column in Multiple Columns.

Related Tips:

Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!

 

Leave your own comment:

*Name:
Email:
  Notify me about new comments ONLY FOR THIS TIP
Notify me about new comments ANYWHERE ON THIS SITE
Hide my email address
*Text:
*What is 5+3 (To prevent automated submissions and spam.)
 
 
           Commenting Terms

Comments for this tip:

John Renwick    29 Jul 2016, 02:58
Such a simple thing: just want to print a long simple list side by side on an excel spreadsheet without adding to forest destruction but have to use a zillion tons of VBA code to do it. Fucking ridiculous!
Rob Page    20 Feb 2016, 12:40
Can this be modified so that instead of writing the cells down the first column, then moving on to the next column, it writes them starting at row 1 across numofcolumns, then moves on to write on the next row? That's the order that I need to data to appear in.

Thanks!
Michelle    10 Feb 2015, 12:46
Great idea to use Word!

I pasted the Excel column into Word. Instead of converting it to text, I formatted the Word page to have two columns, which is what fit my data. It worked very well!
Bonnie    17 Jun 2014, 16:48
I loved the idea of converting to plain text in word - why didn't I think of that?
Great time saver for my purposes!
Daniel    04 Mar 2014, 12:26
Hi Allen.

This code is doing exactly what I want it to do short of one thing, I want to be able to select a cell where I want the output to start.

I removed the "Set wks = Worksheets.Add" making the code paste the output always starting in A1. How can I change this to paste in any cell I prefer?

Best regards
 
 

Our Company

Sharon Parq Associates, Inc.

About Tips.Net

Contact Us

 

Advertise with Us

Our Privacy Policy

Our Sites

Tips.Net

Beauty and Style

Cars

Cleaning

Cooking

DriveTips (Google Drive)

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2016)

Gardening

Health

Home Improvement

Money and Finances

Organizing

Pests and Bugs

Pets and Animals

WindowsTips (Microsoft Windows)

WordTips (Word 97–2003)

WordTips (Word 2007–2016)

Our Products

Helpful E-books

Newsletter Archives

 

Excel Products

Word Products

Our Authors

Author Index

Write for Tips.Net

Copyright © 2016 Sharon Parq Associates, Inc.