View Full Version : Solved: Setting Dynamic Print Range

05-12-2009, 03:33 AM

I need some help in setting up a macro that will set up the print area of my document which will be updated frequently and need to ensure that it will pick up the last row of Column A and the number of Columns will remain the same (Column J).



05-12-2009, 04:01 AM
For just the one sheet? What is in column A, numbers or text?

You can create a dynamic named range. The range name may already exist called Print_Area.

If column A house numbers then change the name to refer to:

If column A house text then change the name to refer to:

05-12-2009, 06:58 AM
Hi JONvdHeyden,

Thanks very much for your help.

That name range works a treat when fired up using a macro.

Cheers mate.


05-12-2009, 07:07 AM
Adding this to the thisworkbook module will set the print area to the used range each time you print.

Option Explicit
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim CF As Long, CV As Long, RF As Long, RV As Long
Dim Col As Long, Rw As Long

With ActiveSheet
CF = .Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
CV = .Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
RF = .Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
RV = .Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

Col = Application.WorksheetFunction.Max(CF, CV)
Rw = Application.WorksheetFunction.Max(RF, RV)

.PageSetup.PrintArea = "$A$1:" & Cells(Rw, Col).Address
End With
End Sub