Setting the Print Area with VBA

When you set the Print Area (File>PageSetup>Sheet Tab), Excel creates a Name called Print_Area. It doesn’t appear to be any more complicated than that because you can create that Name manually and get the same effect. That leaves two ways to set the Print Area in VBA, by accessing the PageSetup object and by accessing the Names collection object.

Sub SPAPageSetup()
    Sheet1.PageSetup.PrintArea = “A1:F15?
End Sub

Sub SPAName()

    Sheet1.Names.Add “Print_Area”, Sheet1.Range(“A1:F15?)
End Sub

To remove the Print Area, you again have two options. Using the PageSetup object, set the PrintArea property to an empty string. For Names, use the Delete method. If the Name doesn’t exist, VBA will raise an error, so it’s best to use some error checking for that method.

Sub DPAPageSetup()

    Sheet1.PageSetup.PrintArea = “” ’empty string
End Sub

Sub DPAName()
    On Error Resume Next
    On Error GoTo 0
End Sub

Posted in Uncategorized

14 thoughts on “Setting the Print Area with VBA

  1. What if the range changes depending on the amount of data transfered into the spreadsheet. I can count the # of columns and rows and set a variable represented by “Var = I9? but I can’t find a way for the Range(“A1:Var”) to be accepted.

  2. make sure there is a printer installed when using the PrintArea procedure – otherwise it will return an error.

  3. What If you would like to print 2 area’s
    on 2 sheets
    somehow i get an error message using
    Sheet1.PageSetup.PrintArea = “A1:F15,A20:F45?
    first range on page1
    second range on page 2

  4. Well the easiest way to do it is probably


    That will printout the range A1:J10

    any of the numbers can be changed for variables.. so you can use that to print out multiple areas on the same sheet onto different pages

  5. How do I set a specific print area using an assigned date? Example:
    Row 8 contains date headers and I want the user to be able to print a date range.

  6. In order to build a range you can also do the following

    Dim strRange as string

    strRange = “A1:N” & Var

    This way you can determine what the number of rows are and set Var to that value.

  7. I am trying to print one long line from a worksheet by breaking it up. A1:G1 on the first line with H1:N1 on the second line using Chr(10) or vbCrLf so that I can maintain the font size. Does anyone know what the syntax would be?

  8. what if i want to set the print area according to what I have selected
    Below is my code, I have made a selection of the cells I want to set and i want to set the print area

    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.PageSetup.PrintArea =

  9. ATTN.: NICK & others

    i like this code of yours, very simple



    so basically 10,10 will change acc to the required area which i need to print. This will be known after a calculation.

    thanks in advance and best regards,

  10. Olá pessoal,

    Estou construindo um código e preciso imprimir as páginas de cada planilha selecionada em uma caixa de listagem, mas somente SE determinado intervalo de células estiver diferente de vazio ( “”). Consigui carregar a listbox, consegui selecionar a planilha, resta agora imprimir, SE e somente SE determinado intervalo de células (Range) em cada uma das 19 páginas estiver DIFERENTE DE VAZIO ( “”). Já quebrei a cabeça tentando mas não consegui até agora.

    Peço ajuda de vc’s.


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.