Code modification help please

G

Guest

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(xlCellTypeLastCell).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
 
G

Guest

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
 
G

Guest

Thanks Tom!

Tom Ogilvy said:
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 said:
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(xlCellTypeLastCell).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
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Print area - selection 4
Slow Macro - Formatting Macro 1
page setup preferances 1
Slow PageSetup macro 3
Print sub returns 4
Printing-don't need half this code but which half?:-) 5
Can't print only 1 page wide-what am I doing wrong? 3
Help with PageSetup for printing, please? 2

Top