Last cell on printed page

[Jan Kronsell]

Is there any way to determine which cell will be the last on a printed page,
no matter if the cell is empty or not.

If I print a page with content in A1 1, the last printed cell on that page
will be I56. If I "zoom" to 80% en Pager Setup the last cell will be K71.

Is there a way to determine which cell will be the last printed before
printing?

Jan

 

[TomPl]

the last cell printed will be based on how you have
defined the print range.

If you have not defined the print range then Excel
will print the "Used Range" on the worksheet.
You can determine the last cell of the "Used Range"
with the following routine.



Sub GetLastCell()

Dim rngUsed As Range

Set rngUsed = ActiveSheet.UsedRange

MsgBox (ActiveSheet.Cells(rngUsed.Rows(rngUsed.Rows.Count). _
Row, rngUsed.Columns(rngUsed.Columns.Count).Column).Address)

End Sub

 

[RyanH]

Rick Rothstein helped me with this. The assumes you have set the print area
in Sheet1. If you have it will tell you the last cell in the Print Area. If
not, it will tell you have not set the print area.

Sub PrintArea2()

Dim myPrintArea As Range
Dim LastRow As Long
Dim LastCol As Long
Const WS As String = "Sheet1"

If Len(Worksheets(WS).PageSetup.PrintArea) > 0 Then

Set myPrintArea = Range(Sheets(WS).PageSetup.PrintArea)

LastRow = myPrintArea.Rows(myPrintArea.Rows.Count).Row
LastCol = myPrintArea.Columns(myPrintArea.Columns.Count).Column

MsgBox Cells(LastRow, LastCol).Address
Else
MsgBox "No Print Area defined yet."
End If

End Sub

Hope this helps!