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!
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.
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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.
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.
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
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.Row
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.Row
Please attach your workbook and/or test this one by pressing Ctrl+Shift+L
It should take you to row 700.
29165110.xlsm
It should take you to row 700.
29165110.xlsm
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
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
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
Premium Content
You need an Expert Office subscription to comment.Start Free Trial