Setting print area in a macro

[LonnieCurrier]

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

[#2 · Accepted solution]

@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

[#3]

Thank you @macrordinary .  That worked very well.  :)