Get range of last visible row

mmmreece

New Member
Joined
Apr 8, 2015
Messages
30
Hi. what I'd like to do is find the last visible row... by this I mean the last row that actually shows on the screen whether or not it contains data.... For example... on the spreadsheet I have open at the moment the last visible row with data is 3409 but the last row showing on the screen is 3418..... I'm essentially importing data from a spreadsheet that is open on one computer (computer A) but the data entry is being performed on a network computer (computer B) and updating visibly via one drive... I then have another spreadsheet open on Computer A that the data is imported into ... what I want to do is then move the visible range down one row on the copy of the spreadsheet ( the original not the one it's importing to) down one row so that I can see when the data is updated before performing the import
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

NoSparks

Well-known Member
Joined
Mar 15, 2013
Messages
1,440
Office Version
  1. 2010
Platform
  1. Windows
Don't know about different screens and that, but this gets the range displayed on my monitor and maybe you can go from there
VBA Code:
Sub OnScreenRange()

    Dim rngStr As String, ray As Variant

    ray = Split(Replace(ActiveWindow.ActivePane.VisibleRange.Address, ":", ""), "$")
    rngStr = "Range(" & Chr(34) & ray(1) & ray(4) & ":" & ray(3) & ray(4) & Chr(34) & ")"
    MsgBox "Last row on screen is" & vbLf & rngStr
    ActiveWindow.ScrollRow = ray(4) - 10
    
End Sub
 

mmmreece

New Member
Joined
Apr 8, 2015
Messages
30
Thanks. I was only really looking to get the visible address as I'd've written something for the rest. I only included all the info so you could see what I was doing. I shall give this a go tomorrow when I'm a bit more awake... Thanks
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,514
Messages
5,854,175
Members
431,623
Latest member
ncorkren

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top