Dynamic print area
 

I am having trouble creating a dynamic print area. I need to print a sheet
that is updated from other sheets in the same workbook. There will always be
data in columns A-G and a variable number of rows.

LngLastRow = ActiveSheet.Range("G65536").End(xlUp).Row
ActiveSheet.Range("A1:G" & LngLastRow).Select
ActiveSheet.PageSetup.PrintArea = ("A1:G" & LngLastRow)

I use the above code to establish the range of cells used and to then
establish the print area. It appears to work because dashed lines are around
the selected range, but it does not print as I had hoped. I was expecting, in
this case, to have 2 printed sheets , but I have 4 printed sheets with
columns F & G on pages 3 and 4.

TIA

Dynamic print area
 

Hi Harley

You can change the margins in pagesetup and maybe

.PageSetup.FitToPagesWide = 1



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Harley" wrote in message ...
I am having trouble creating a dynamic print area. I need to print a sheet
that is updated from other sheets in the same workbook. There will always be
data in columns A-G and a variable number of rows.

LngLastRow = ActiveSheet.Range("G65536").End(xlUp).Row
ActiveSheet.Range("A1:G" & LngLastRow).Select
ActiveSheet.PageSetup.PrintArea = ("A1:G" & LngLastRow)

I use the above code to establish the range of cells used and to then
establish the print area. It appears to work because dashed lines are around
the selected range, but it does not print as I had hoped. I was expecting, in
this case, to have 2 printed sheets , but I have 4 printed sheets with
columns F & G on pages 3 and 4.

TIA

Dynamic print area
 

Harley,
Try something like this. I'm not one of the experts, but this worked
for me. Adapt as necessary.

Option Explicit
Private Sub PrintArea()
Dim LngLastRow As Long

LngLastRow = ActiveSheet.Range("G65536").End(xlUp).Row
ActiveSheet.Range("A1:G" & LngLastRow).Select

With ActiveSheet.PageSetup
..PrintArea = ("A1:G" & LngLastRow)
..FitToPagesWide = 1
..FitToPagesTall = False

End With
End Sub

HTH


--
Casey


------------------------------------------------------------------------
Casey's Profile: http://www.excelforum.com/member.php...fo&userid=4545
View this thread: http://www.excelforum.com/showthread...hreadid=483666

Dynamic print area
 

Thank you Ron and Casey. The code now works as I had hoped.