Last row and column in print area

Pestomania

Board Regular
Joined
May 30, 2018
Messages
145
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.

Code:
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?
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,343
Below is a function to give you the bottom-right cell of the print area:
Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,204
Messages
5,527,396
Members
409,759
Latest member
KCH

This Week's Hot Topics

Top