Forum Discussion

LonnieCurrier's avatar
LonnieCurrier
Copper Contributor
May 18, 2020
Solved

Setting print area in a macro

How can I automatically set the print area in a macro? I recorded a macro that saved the workbook, selected the cells that contained data (by selecting cell A1, then pressing Ctrl-End to go to the “LastCell”), set the print area, then sets “Fit All Columns on One Page”. The macro seems to hardcode the “LastCell” value of the workbook (in this case F429). When I run the macro on a larger workbook, the print area ends on row 429 and nothing past that prints. Is there a way to use a variable in place of $F$429 so that the print area is automatically set to print all active rows that contain data? Here is my current macro:

 

   Range("A1").Select

   Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select

   ActiveSheet.PageSetup.PrintArea = "$A$1:$F$429"

   Range("A1").Select

   Application.PrintCommunication = False

   With ActiveSheet.PageSetup

       .PrintTitleRows = "$1:$2"

       .PrintTitleColumns = ""

   End With

   Application.PrintCommunication = True

   ActiveSheet.PageSetup.PrintArea = "$A$1:$F$429"

   Application.PrintCommunication = False

   With ActiveSheet.PageSetup

       .LeftMargin = Application.InchesToPoints(0.75)

       .RightMargin = Application.InchesToPoints(0.75)

       .TopMargin = Application.InchesToPoints(0.75)

       .BottomMargin = Application.InchesToPoints(0.5)

       .HeaderMargin = Application.InchesToPoints(0.5)

       .FooterMargin = Application.InchesToPoints(0.5)

       .FitToPagesWide = 1

       .FitToPagesTall = 0

       .ScaleWithDocHeaderFooter = True

   End With

   Application.PrintCommunication = True

Macros and VBA
  • macrordinary's avatar
    macrordinary
    May 18, 2020

    LonnieCurrier 

    In this case, you need to create a variable (I've named mine rngPrint) and assign the end of the active cells to it. Then, you can assign the PrintArea based on the address of the variable you've created.

     

    Code snippet is below.

     

    Dim rngPrint As Range

    Range("A1").Select
    Set rngPrint = Range(Selection, ActiveCell.SpecialCells(xlLastCell))  'This was the Ctrl-End
    ActiveSheet.PageSetup.PrintArea = rngPrint.Address

2 Replies

  • macrordinary's avatar
    macrordinary
    Brass Contributor
    May 18, 2020

    LonnieCurrier 

    In this case, you need to create a variable (I've named mine rngPrint) and assign the end of the active cells to it. Then, you can assign the PrintArea based on the address of the variable you've created.

     

    Code snippet is below.

     

    Dim rngPrint As Range

    Range("A1").Select
    Set rngPrint = Range(Selection, ActiveCell.SpecialCells(xlLastCell))  'This was the Ctrl-End
    ActiveSheet.PageSetup.PrintArea = rngPrint.Address

Resources