Last row and column in print area

[Pestomania]

Hi! I am trying to determine the "white space" between the last used row and the last row in print area. I currently have this to determine last used row and it works, but can't determine last row in print area.

Sub cover()
Dim lastrow as long
Dim top as string

Lastrow = range("A" & rows.count).End(xlUp).offset(1).row
Top =Range ("A" & lastrow).top

ActiveSheet.Shapes.AddShape(msoShapeRectangle, 0, top, x, y)

End sub

Can someone help me determine x and y to keep the shape from going outside the print area?

 

[bobsan42]

Below is a function to give you the bottom-right cell of the print area:

Function PrAreaBottomRight(sh1 As Worksheet) As Range
    With sh1
        Dim strPA As String: strPA = .PageSetup.PrintArea
        If strPA = "" Then 'No PrintArea Definition for this sheet - Excel will set PrintArea automatically based on the UsedRange
            Set PrAreaBottomRight = Nothing
            Exit Function
        Else
            Set PrAreaBottomRight = .Range(strPA)
        End If
    End With
    With PrAreaBottomRight
        Dim rr As Long: rr = .Rows.Count
        Dim cc As Long: cc = .Columns.Count
        PrAreaBottomRight = .Resize(1, 1).Offset(rr - 1, cc - 1)
    End With
End Function

The result of the function is a Range type object - you can then extract coordinates and size from it.
The function takes a worksheet object as argument.
If there is no PrintArea definition for this sheet - the function will return Nothing.