Solved: Setting Dynamic Print Range

[bananatang]

Hi,

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).

Thanks

BT

[JONvdHeyden]

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:
=!$A$1:INDEX(!$J:$J,MATCH(9.99999999999999E+307,!$A:$A))

If column A house text then change the name to refer to:
=!$A$1:INDEX(!$J:$J,MATCH(REPT("Z",255),!$A:$A))

[bananatang]

Hi JONvdHeyden,

Thanks very much for your help.

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

Cheers mate.

BT

[lucas]

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