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

Printing a Single Column in Multiple Columns

by Allen Wyatt
(last updated June 23, 2018)

2

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 are all 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 fewer 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://dmcritchie.mvps.org/excel/snakecol.htm

Note:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (8239) applies to Microsoft Excel 2007, 2010, 2013, and 2016. You can find a version of this tip for the older menu interface of Excel here: Printing a Single Column in Multiple Columns.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Header and Footer Formatting Codes

When creating headers and footers in an Excel worksheet, you can use special codes to add or format information. This tip ...

Discover More

Printing a Portion of a Worksheet

Need to print a portion of a worksheet, but don't want to waste paper by printing the whole thing? It's easy to print ...

Discover More

Missing Top and Bottom Margins

You get your document set up just the way you want it, and then notice that all of a sudden Word doesn't show any top or ...

Discover More

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

More ExcelTips (ribbon)

Adding Page Borders to a Printout

Need a full-page border on your Excel printouts? It's not as easy to get one as you might wish. There are a few ways you ...

Discover More

Printing Multiple Worksheets on a Single Page

Got a bunch of worksheets and you want to save paper by printing multiple worksheets on a single piece of paper? There ...

Discover More

Printing without Opening

Want to print one ore more workbooks without the need of actually opening the file? It's easy to do when you rely on ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 9 + 2?

2018-08-02 16:19:05

Thomas Papavasiliou

If anyone is interested in a more elaborate macro that prepares a long and narrow data range for printing may use the macro bellow.

Macro prompts user to define, if any, any header rows.

Sub paginate_for_print()

'Macro written 21/06/2014

'If user prefers to let an apparently empty column between the moved data, he can type a space
'in the first to the right empty cell, next to existing data, set that column to the desired width and run the macro
'As this macro is designed to limit the number of printed pages, it runs on a copy of the original sheet
'Original data sheet remains unaltered

' Variable definition
Dim sr As Long, er As Long, rh As Long, hpb As Long, r As Long
Dim sc As Integer, ec As Integer, cc As Integer, ans As Integer, he_rows As Integer, v_pbreaks As Integer
Dim tlt As String, msg As String, he_ro As String, head_to_rep As String
Dim cell As Variant

' Collection of used range limits and columns
With ActiveSheet.UsedRange
sr = .Row
sc = .Column
er = .Rows.Count + sr - 1
ec = .Columns.Count + sc - 1
cc = .Columns.Count
End With

' Initial message and choices for header rows
msg = "This macro will create a new sheet stacking data " + Chr(13)
msg = msg & "for printing purposes." & Chr(13)
msg = msg & "Data row height must be identical." & Chr(13) & Chr(13)
msg = msg + "Do you have any header rows?"
tlt = "Please answer the question..."
ans = MsgBox(msg, vbYesNoCancel + vbQuestion, tlt)

If ans = 2 Then
msg = "Cancel button selected Macro ends."
tlt = "Good choice!"
MsgBox msg, vbOKOnly + vbInformation, tlt
Exit Sub
End If

On Error Resume Next
If ans = 6 Then
msg = "Select header rows or cell/s within header rows."
tlt = "Please follow instructions..."
he_ro = Application.InputBox(msg, Default:=Rows(sr).Address, Title:=tlt, Type:=8).Address
he_rows = Range(he_ro).Rows.Count
head_to_rep = Range(Cells(sr, sc), Cells(sr + he_rows - 1, ec)).Address

If IsEmpty(he_ro) Then
tlt = "Wise decision..."
msg = "Cancel button selected Macro ends."
MsgBox msg, vbOKOnly + vbInformation, tlt
Exit Sub
End If
Else
he_rows = 0
End If

On Error GoTo errorhandling

' Control of identical data rows height (headers excluded)
rh = Range(Cells(sr + he_rows, 1), Cells(er, 1)).RowHeight
If IsNull(Range(Cells(sr + he_rows, 1), Cells(er, 1)).RowHeight) Then
For Each cell In Range(Cells(sr + he_rows, 1), Cells(er, 1))
If cell.RowHeight <> cell.Offset(1, 0).RowHeight Then
cell.Range("a1:a2").EntireRow.Select
msg = "Data row height in rows " & cell.Row & " and " & cell.Row + 1 & " is not identical. Macro stops."
tlt = "Correct the error and try again! "
MsgBox msg, vbOKOnly + vbCritical, tlt
Exit Sub
End If
Next
End If

' Control of existance of data that could be condensed for printing
Application.ScreenUpdating = False 'Accelerates macro execution

Sheets(ActiveSheet.Name).Copy after:=Sheets(ActiveSheet.Name)

ActiveWindow.View = xlPageLayoutView 'This is necessary to display the page breaks

If ActiveSheet.HPageBreaks.Count > 0 Then
hpb_r = ActiveSheet.HPageBreaks(1).Location.Row
Else
tlt = "No need to paginate..."
MsgBox "Sheet is short. Macro ends", vbOKOnly + vbExclamation, tlt
Application.DisplayAlerts = False
ActiveWindow.ActiveSheet.Delete 'Deleting created copy of the original sheet
Application.ScreenUpdating = True
Exit Sub
End If

ActiveWindow.View = xlNormalView

v_pbreaks = 0

' Main part of the macro
Do While Application.WorksheetFunction.CountA(Rows(hpb_r)) > 0
Range(Cells(hpb_r, sc), Cells(er, ec)).Cut Destination:=Cells(sr + he_rows, ec + 1)

' Repeating header rows
If ans = 6 Then
Range(head_to_rep).Copy Destination:=Cells(sr, ec + 1)
End If

' Establishing the same format
Range(Cells(sr, sc), Cells(sr, ec)).EntireColumn.Copy
Cells(1, sc).Offset(0, cc).Range("A1").PasteSpecial Paste:=xlPasteFormats

Application.CutCopyMode = False

If ActiveSheet.VPageBreaks.Count > v_pbreaks And cc > 1 Then

' Inserting, if necessary, a vertical page break to keep the group of transferred data un-split on the same sheet
ActiveWindow.SelectedSheets.VPageBreaks.Add Before:=Cells(1, ec + 1)
v_pbreaks = v_pbreaks + 1
End If

sc = sc + cc
ec = ec + cc
Loop

' Comment any of the following two lines to fit your preference
' ActiveWindow.DisplayGridlines = True
ActiveWindow.DisplayGridlines = False

Application.ScreenUpdating = True

Range("a1").Select

Exit Sub

errorhandling:
msg = "Macro encountered either a non-adequate data input or an unexpected error" + Chr(10)
msg = msg + "Try to re-run checking original data and selections" + Chr(10)
msg = msg + "If the error persists, contact the programmer" + Chr(10) + Chr(10)
msg = msg + "Thomas Papavasiliou" + Chr(10)
msg = msg + "e-mail thpapavasiliou@yahoo.com"
MsgBox msg, vbOKOnly + vbCritical, "If I am still alive, I will answer."

End Sub



2018-06-23 05:39:21

Willy Vanhaelen

Here is a relatively simple formula if you don't want to use a macro or the bypass via Word.

If your list to print is in column A and you want to print it in 4 columns of 40 rows each, then enter this formula in B1:
=INDIRECT(ADDRESS(ROW()+40*COLUMN(A1),1))
Leave the cellpointer in B1 and extent your selection to range B1:D40, press F2 and Ctrl+Enter.
And voila there is your list to print.
Select it (A1:D40) and in the Print Dialog check the "Selection" option in the Print What frame.
You are ready to print.

If you want to change the number of rows, change 40 in ...ROW()+40... to the number you want. For 25 rows:
=INDIRECT(ADDRESS(ROW()+25*COLUMN(A1),1))
The number of columns depends simply on your selection.

There is no need to use another sheet. After printing you can delete the formulas and your sheet is back to what it was before.


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.