Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. 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 December 12, 2017)

5

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

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (8239) applies to Microsoft Excel 2007, 2010, and 2013. 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

Throwing Out the Lowest Score

Want to add up a bunch of scores, without including the lowest one in the bunch? You can make a small change to your ...

Discover More

Conditional Calculations in Word

Word allows you to insert simple formulas, using fields, in table cells. You can also create simple conditional ...

Discover More

WindowsTips Annual Archives

WindowsTips is a weekly newsletter that provides tips on how to effectively use the latest Windows operating ...

Discover More

Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!

More ExcelTips (ribbon)

Specifying a Print Tray for a Worksheet

If you need to modify where a worksheet is printed (meaning, which paper tray it should use), Excel doesn't provide a lot ...

Discover More

Printing a Chart

It is inevitable that if you spend time creating a chart you will want to print that chart on your printer. Here's how ...

Discover More

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
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 + 6?

2018-02-17 12:28:02

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.


2017-12-13 17:24:22

Thomas Papavasiliou

A general utility for such a print is stated in the following macro that allows to define header row or rows and checks that the data row have the same row height.

If you want to separate the areas in printing, set the column width of the first empty to the right column to the desired as separator width and type a space in any row of that column.

Here is the macro:

Sub paginate_for_print()

'Macro written 21/06/2014 by Thomas Papavasiliou

'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
'Macro, for aesthetic reasons, controls that all data (non header) rows have identical height
'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

'Outmoded (but still available) Excel 4 function to determine the number of total printed pages in a worksheet.
'Useless for the present macro. Stated for memory only
' IPages = ExecuteExcel4Macro("Get.Document(50)")
'Other code lines that may be useful for other cases are



Application.ScreenUpdating = True

' Variable definition
Dim sr As Long
Dim er As Long
Dim rh As Long
Dim hpb_r As Long
Dim sc As Integer
Dim ec As Integer
Dim cc As Integer
Dim ans As Integer
Dim he_rows As Integer
Dim v_pbreaks As Integer
Dim tlt As String
Dim msg As String
Dim he_ro As String
Dim 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


2016-04-04 09:13:57

Dennis Costello

One could also create, in the recommended separate tab or workbook, a flock of link formulae - =tab!A1, =tab!A2, =tab!A3, etc. in column A, =tab!A61, =tab!62, etc. in column B.

To keep it a bit neater, you could expand the formulae a bit to
=if(isempty(tab!a1), "", tab!a1)

One could also of course expand on the notion to include the column header, too - all that is left as an exercise for the reader, who can "season to taste".


2016-04-04 08:57:32

Dennis Costello

I'm not sure of the context where you're using the string "[V1][AD68]", but it sounds like you want to specify a Print Area. The easy way to do that is to select the range you want to print, and then click Page Layout -> Print Area -> Set Print Area.

If you're trying to do this in VBA, by specifying the value for a worksheet property, use the string "V1:AD68" to specify the range.


2016-04-03 15:52:31

Richard T. Prinzing

I want to print, on my Microsoft Excel spreadsheet, boxes V 1 through AD 68 ONLY. When I attempt to print these spaces, I receive the message "Integer is not valid" with an [OK] underneath.

How do I print just those columns? I have tried [V1] [AD68], [V 1] [AD 68], and [V,1] [AD,68] to no avail. I even punched in the "print what" section the "selection" rather than the "entire workbook."

How do I print just those nine columns?


















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.