Code modification help please
 

Hi all,
I could sure use some help with the following code. I'm using this to set
the print area on a sheet. The problem is, that it is shrinking the selection
WAY too small - not to "fit to page" as I was wanting it to. Any
suggestions???

Thanks,
Tom

Lrow = ActiveSheet.Range("A:O").SpecialCells(xlCellTypeLa stCell).Row
With ActiveSheet.PageSetup
.PrintArea = "$A$1:$O$" & Lrow
.Zoom = False
.LeftMargin = Application.InchesToPoints(0.5)
.RightMargin = Application.InchesToPoints(0.5)
.TopMargin = Application.InchesToPoints(0.5)
.BottomMargin = Application.InchesToPoints(0.5)
.FitToPagesWide = 1
.FitToPagesTall = 1
End With

Code modification help please
 

I suspect that lrow is overstated.

sub SetPrintArea()
Dim Lrow as Long
Lrow = GetRealLastCell(Activesheet).row
With ActiveSheet.PageSetup
.PrintArea = "$A$1:$O$" & Lrow
.Zoom = False
.LeftMargin = Application.InchesToPoints(0.5)
.RightMargin = Application.InchesToPoints(0.5)
.TopMargin = Application.InchesToPoints(0.5)
.BottomMargin = Application.InchesToPoints(0.5)
.FitToPagesWide = 1
.FitToPagesTall = 1
End With

End Sub

Function GetRealLastCell(sh as Worksheet) as Range
Dim RealLastRow As Long
Dim RealLastColumn As Long
On Error Resume Next
RealLastRow = _
sh.Cells.Find("*", sh.[A1], , , xlByRows, xlPrevious).Row
RealLastColumn = _
sh.Cells.Find("*", sh.[A1], , , xlByColumns, xlPrevious).Column
set GetRealLastCell = sh.Cells(RealLastRow, RealLastColumn)
End Function

--
Regards,
Tom Ogilvy


"Tom" wrote:

Hi all,
I could sure use some help with the following code. I'm using this to set
the print area on a sheet. The problem is, that it is shrinking the selection
WAY too small - not to "fit to page" as I was wanting it to. Any
suggestions???

Thanks,
Tom

Lrow = ActiveSheet.Range("A:O").SpecialCells(xlCellTypeLa stCell).Row
With ActiveSheet.PageSetup
.PrintArea = "$A$1:$O$" & Lrow
.Zoom = False
.LeftMargin = Application.InchesToPoints(0.5)
.RightMargin = Application.InchesToPoints(0.5)
.TopMargin = Application.InchesToPoints(0.5)
.BottomMargin = Application.InchesToPoints(0.5)
.FitToPagesWide = 1
.FitToPagesTall = 1
End With

Code modification help please
 

Thanks Tom!

"Tom Ogilvy" wrote:

I suspect that lrow is overstated.

sub SetPrintArea()
Dim Lrow as Long
Lrow = GetRealLastCell(Activesheet).row
With ActiveSheet.PageSetup
.PrintArea = "$A$1:$O$" & Lrow
.Zoom = False
.LeftMargin = Application.InchesToPoints(0.5)
.RightMargin = Application.InchesToPoints(0.5)
.TopMargin = Application.InchesToPoints(0.5)
.BottomMargin = Application.InchesToPoints(0.5)
.FitToPagesWide = 1
.FitToPagesTall = 1
End With

End Sub

Function GetRealLastCell(sh as Worksheet) as Range
Dim RealLastRow As Long
Dim RealLastColumn As Long
On Error Resume Next
RealLastRow = _
sh.Cells.Find("*", sh.[A1], , , xlByRows, xlPrevious).Row
RealLastColumn = _
sh.Cells.Find("*", sh.[A1], , , xlByColumns, xlPrevious).Column
set GetRealLastCell = sh.Cells(RealLastRow, RealLastColumn)
End Function

--
Regards,
Tom Ogilvy


"Tom" wrote:

Hi all,
I could sure use some help with the following code. I'm using this to set
the print area on a sheet. The problem is, that it is shrinking the selection
WAY too small - not to "fit to page" as I was wanting it to. Any
suggestions???

Thanks,
Tom

Lrow = ActiveSheet.Range("A:O").SpecialCells(xlCellTypeLa stCell).Row
With ActiveSheet.PageSetup
.PrintArea = "$A$1:$O$" & Lrow
.Zoom = False
.LeftMargin = Application.InchesToPoints(0.5)
.RightMargin = Application.InchesToPoints(0.5)
.TopMargin = Application.InchesToPoints(0.5)
.BottomMargin = Application.InchesToPoints(0.5)
.FitToPagesWide = 1
.FitToPagesTall = 1
End With