Avatar of SteveL13
SteveL13
Flag for United States of America asked on

Get to the last filled in row in a worksheet

How can I get to the last filled in row in a worksheet?  I'm sure there must be a keyboard shortcut.
Microsoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
Martin Liss

8/22/2022 - Mon
Martin Liss

Ctrl+End key
SteveL13

ASKER
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 Liss

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

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
byundt

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
SteveL13

ASKER
I don't understand why I can't just go to the last filled in cell in the worksheet.
ASKER CERTIFIED SOLUTION
Martin Liss

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
byundt

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Martin Liss

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
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.