Get to the last filled in row in a worksheet

SteveL13
SteveL13 used Ask the Experts™
on
How can I get to the last filled in row in a worksheet?  I'm sure there must be a keyboard shortcut.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Ctrl+End key

Author

Commented:
That doesn't seem to be working.  It takes me to row 1042724 which is empty but I can see empty rows under that one.
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Copy/paste this macro into a module in your workbook. Change the A1048576 to B1048576 or whatever column letter that will always have data in the last cell. Assign a hot key to that macro. If you need them, here is how to do that.

In Excel, Press Alt+F11 to open the Visual Basic Editor (VBE)

Right-click on your workbook name in the "Project-VBAProject" pane. If you don’t see an existing module then select Insert -> Module from the menu bar. Otherwise just select the module.

Copy the macro (you can use the ‘Select All’ button if you like) and paste it into the right-hand pane of the VBA editor ("Module1" window)

Press Alt+F11 again to go back to Excel

Optionally, press Alt+F8 to open the "Macro" dialog window. Select the macro, click ‘Options…’,  hold down the Shift key and type the letter A (or any other letter) and click ‘OK’. Then anytime you want to run the macro press Ctrl+Shift+A.

When you close the workbook you will need to save it as an xlsm, xlsb or xls file if it’s not already one of those.

Sub LastRow()
Dim lngLastRow As Long
Dim lngRow As Long
Dim r As Range

lngLastRow = Range("A1048576").End(xlUp).Row
Application.GoTo Cells(lngLastRow, 1)

' Revove selection from sight
Application.ScreenUpdating = False
Set r = Application.ActiveWindow.VisibleRange
r(r.Cells.Count + 1).Select
Application.ScreenUpdating = True

End Sub

Open in new window

Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
The F5...Special Cells dialog lets you go to the Last Cell. Its keyboard shortcut is F5 ALT S S (Enter)

Note that Excel sometimes loses track of where the last cell is located. When that happens, you will see the vertical scrollbar take you way beyond the last row of data. In such cases, if you delete the intervening rows, save the file, close the file and reopen it, the scrollbar will be working accurately and the Special Cells...Last Cell method works.

If you don't mind diving into VBA after you have deleted those blank rows, executing the following statement in the Immediate Window will also reset the scrollbar and Last Cell without need to save, close and reopen the workbook:
?ActiveSheet.UsedRange.Rows.Count

Author

Commented:
I don't understand why I can't just go to the last filled in cell in the worksheet.
Older than dirt
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
Please attach your workbook and/or test this one by pressing Ctrl+Shift+L
It should take you to row 700.
29165110.xlsm
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
Commented:
I don't understand why I can't just go to the last filled in cell in the worksheet.
Excel keeps track of the bottom right cell in the used range on a worksheet. You wanted a keyboard shortcut: that's where F5 ALT S S (Enter) will take you. If that cell is blank, then ALT Left Arrow will take you from that location to the last cell that was filled in on that row.

Rather than remember all of those keyboard shortcuts, I would assign a keyboard shortcut like CTRL + Shift + L to a macro. You may assign a keyboard shortcut using the ALT + F8 macro selector dialog by selecting the macro, then clicking the Options... button.
Sub LastDatum()
Dim n As Long
With ActiveSheet
    n = .UsedRange.Rows.Count   'Sometimes needed to reset the scrollbars and last cell location
    .Cells(n + .UsedRange.Row - 1, .Columns.Count).End(xlToLeft).Select
End With

End Sub

Open in new window

Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
You’re welcome and I’m glad I was able to help.

If you expand the “Full Biography" section of my profile you’ll find links to some articles I’ve written that may interest you.

Marty - Microsoft MVP 2009 to 2017
              Experts Exchange Most Valuable Expert (MVE) 2015, 2017
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2018
              Experts Exchange Top Expert VBA 2018
              Experts Exchange Distinguished Expert in Excel 2018

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial