Determine tje last visible row on the screen.

  • Thread starter Thread starter SpeeD
  • Start date Start date
S

SpeeD

Hi.

I need to determine the last visible row on the screen.
Im not talking about visible true/false, but the last row a user can see in
is monitor.
Screen Resolution can change and i really need to know the row.

Can this be done??

Thanks
 
I dont think there is a direct way..However you could try the below two
methods.

'If all rows are of the same height you can try
Msgbox Fix(Activewindow.Usableheight/rows(1).height)

'If not try the below macro
Sub LastVisibleRow()
Dim lngRow As Long
Do: lngRow = lngRow + 1
Loop Until ActiveWindow.UsableHeight < _
Range("A" & lngRow).Top + Range("A" & lngRow).Height
MsgBox "LastRow displayed on screen is row " & lngRow - 1
End Sub

If this post helps click Yes
 
As long as you are not near the very bottom of the worksheet (that is, Row
65536 for XL2003, Row 1048576 for XL2007), I think this will work...

With ActiveWindow
.LargeScroll 1
LastVisibleRow = ActiveWindow.ScrollRow - 1
ActiveWindow.LargeScroll , 1
End With

where the bottom displayed row will be place in the LastVisibleRow variable.
 
I would point out that the major difference between my code and Jacob's
(other than his uses looping and mine does not) appears to be this... my
code returns the row number for the last *fully* visible row (even if there
is part of another row partially visible below it) whereas Jacob's returns
the row number for whatever row is on the bottom of the (visible part of
the) worksheet even if that row is not fully visible.
 
Fisrt off, all thank you all.

Im going to try all your code examples and see what works best in my case.

You guys are the best!

SpeeD
 
As long as you are not near the very bottom of the worksheet (that is,
Why don't use that :

LastVisibleRow = ActiveWindow.ActivePane.VisibleRange.Rows.Count +
ActiveWindow.ScrollRow - 1
Click to expand...

The reason I didn't do that is because I didn't think of it.<g> However, I
am glad you re-activated this thread as we both overlooked something... what
if the Window (Screen) is Split (not Frozen, but Split) and one of the top
Panes is active? In that case, the reported last row will be incorrect. I
think this code will return the correct result no whether the window is
split or not and no matter which Pane is active...

With ActiveWindow
With .Panes(.Panes.Count)
LastVisibleRow = .VisibleRange.Rows.Count + .ScrollRow - 1
End With
End With

Rick Rothstein (MVP - Excel)
 
Back
Top