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

not as far as i know.

"SpeeD" wrote:

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
---------------
Jacob Skaria


"SpeeD" wrote:

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

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.

--
Rick (MVP - Excel)


"SpeeD" wrote in message
...
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 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.

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
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.

--
Rick (MVP - Excel)


"SpeeD" wrote in message
...
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

Rick Rothstein wrote on 09/30/2009 09:19 ET :
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.

Rick (MVP - Excel)


&quot;SpeeD&quot; wrote in message
news:
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



Hello Rick,

Why don't use that :

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

Thanks

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.

Why don't use that :

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

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)