Printing non-contiguous areas on one page

Important message to visitors of this page

Ron de Bruin decided to remove all Windows Excel content from his website for personal reasons. If you want to know why, head over to rondebruin.nl.

Luckily, Ron was kind enough to allow me to publish all of his Excel content here.

Most of these pages are slightly outdated and may contain links that don 't work. Please inform me if you find such an error and I'll try to fix it.

Kind regards

Jan Karel Pieterse

Insert a new worksheet (you're going to use this only for printing). Do this for each area you want to print on the same page, the areas don’t have to be on the same sheet. If you change the original range, the picture will change too (values and formatting!). You can always use this sheet to print because it will always update if you change the original data.

Excel 97-2003

Select the range
Edit | Copy in the menu bar (or use Ctrl-C)
Go to the new worksheet and with the Shift key pressed click on Edit | Paste Picture Link.
Go back and do the same for the other areas.

Note: You see that the edit menu will change when you press the shift button when you click on Edit.

Excel 2007-2013

Select the range
Home tab | Clipboard group | Copy (or use Ctrl-C)
Go to the new worksheet and Click on the arrow on the Paste button on the Home Tab

Excel 2007: Choose As Picture >Paste Picture Link
Excel 2010-2013: Choose the Linked picture button.

Go back and do the same for the other areas.

Print selection or range with one or more areas with a macro

The macro will add a new sheet and copy all the selection areas on it. Then it will print and delete the sheet. You can also enter the more areas range in the code if you want.

Sub Test()
    Dim Destrange As Range
    Dim Smallrng As Range
    Dim Newsh As Worksheet
    Dim Ash As Worksheet
    Dim Lr As Long

    Application.ScreenUpdating = False

    Set Ash = ActiveSheet
    Set Newsh = Worksheets.Add
    Ash.Select

    Lr = 1

    'You can also use a range with more areas like this
    'For Each smallrng In Ash.Range("A1:C1,D10:G20,A30").Areas

    For Each Smallrng In Selection.Areas
        Smallrng.Copy
        Set Destrange = Newsh.Cells(Lr, 1)
        Destrange.PasteSpecial xlPasteValues
        Destrange.PasteSpecial xlPasteFormats
        Lr = Lr + Smallrng.Rows.Count
    Next Smallrng

    Newsh.Columns.AutoFit

    Newsh.PrintOut

    Application.DisplayAlerts = False
    Newsh.Delete
    Application.DisplayAlerts = True

    Application.ScreenUpdating = True

End Sub

Ron de BruinExcel Automation