Determine Visible Worksheet Area Size in Points

Question

I am trying to determine the visible worksheet area in points. So far, I have tried the Application.Width, but that gives me the entire area including the scroll bars, and likewise Application.Height includes the ribbon and horizontal scroll bar. What I need specifically is the size of only this viewable worksheet area.

Something I have tried so far is looking at the visible range. For example, if I can see A1 thru AA40, then I can use the code

 Debug.Print ws.Range("A1:AA40").Width
 Debug.Print ws.Range("A1:AA40").Height

The only problem with this is if I can see half of column AB and half of row 41, then I don't have the full size. Also, it is not adjustable if I decide to change the application window size to half the screen.

I have no need for converting into the number of pixels, I just need the point sizes. Does anyone know a way to get the visible area size, or at the very least how to resize a range to fit only this visible area?

Answer 1

You can access the Windows collection of Application and use the Width and Height properties of items of that collection. E.g.

Debug.Print Application.Windows(1).Width
Debug.Print Application.Windows(1).Height

You can also refer to ActiveWindow and get the properties from that as well.

Example: