Last updated on June 12, 2018 By Victor Chan
Summary: Learn four different ways to find the last cell, row or column in a worksheet using Excel VBA. The method to choose depends on your data layout, and if the sheet contains blank cells.
Difficulty: Intermediate
Video: Watch on YouTube [Click here]
Download the sample file with VBA code:
Download the sample file with VBA code (change row height)
Finding the last used cell, row or column is a very useful technique when writing Macros and VBA applications.
One way to write VBA Macro code means you always need to go back into the code and modify range references whenever your data set changes.
A better way to write VBA Macro code is to dynamically determine the size of raw data instead of putting fixed range references.
… and choosing the right way for you mostly depends on what your data looks like, and what level of VBA complexity you can handle.
Click on the links to learn more about each way:
Excel VBA Cheat Sheets » Find it hard to write Excel VBA Macros? Think there’s too much to remember? You’re not alone! Check out my convenient one-page guides. They’ll help you write Excel VBA Macros better and faster.
Click here to find out more…
The SpecialCells method does the same thing as pressing the Ctrl+End keyboard shortcut and selects the last used cell on the sheet.
Sub Method_1_Range_SpecialCells() ' PURPOSE : Find last used cell on Active Sheet ActiveSheet.UsedRange 'Refresh Used Range MsgBox "Last Used Cell :" & _ Range("A1").SpecialCells(xlCellTypeLastCell).Address End Sub
Download the sample file with VBA code:
Here’s the Microsoft reference for the Range.SpecialCells Property (Excel):
This is the easiest way to find the last used cell. Just remember that using the Range.SpecialCells method finds the last used cell, which can be different than the last non-blank cell.
When you’re in Excel you can hit CTRL + End on the keyboard and be taken to some cell right at the bottom of the sheet that has no data in it.
This could happen for a couple of reasons. One common reason is the formatting properties for that cell have been changed from default. Just by changing the font, font size or fill color of a cell will flag it as a used cell.
The Range.End property is very similar to pressing the CTRL + Arrow Key keyboard shortcut. In VBA we can use this property to find the last non-blank cell in a single row or column.
Sub Method_2_Range_End() 'Finds the last non-blank cell in a single row or column Dim Last_Row As Long Dim Last_Col As Long 'Find the last non-blank cell in column A Last_Row = Cells(Rows.Count, 1).End(xlUp).Row 'Find the last non-blank cell in row 1 Last_Col = Cells(1, Columns.Count).End(xlToLeft).Column MsgBox "Last Row: " & Last_Row & vbNewLine & _ "Last Column: " & Columns(Last_Col).Address(False, False) End Sub
Download the sample file with VBA code:
Here’s the Microsoft reference for the Range.End Property (Excel):
To find the last non-blank cell in a column, this technique starts at the last cell in the column and goes up (xlUp) until it finds the last non-blank cell.
The Rows.Count statement returns a count of all the rows in the worksheet. So we’re just specifying the last cell in column A of the sheet (cell A1048567), and going up until we find the last non-blank cell.
It’s the same with finding the last non-blank cell in a row.
The technique starts at the last column of row 1, then goes to the left (xlToLeft) until the last non-blank cell is found in the row.
The Columns.Count statement returns the total number of columns in the sheet. So we start at the last column and go left.
The parameter for the End property specifies which direction to go. The options are: xlDown, xlUp, xlToLeft, xlToRight.
The Range.Find method is useful when your data set has blanks. Use it to find the last non-blank row or column on a worksheet
The Range.Find method is versatile but when you first see all the parameters you might feel overwhelmed…
But don’t worry… the Range.Find method is almost the same as using the Find and Replace dialog box in Excel (Keyboard shortcut CTRL + F)
Sub Method_3_Range_Find_Row() ' PURPOSE : Find last non-blank row on Active Sheet Dim Last_Row As Long On Error Resume Next Last_Row = Cells.Find(What:="*", _ After:=Range("A1"), _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious).Row On Error GoTo 0 MsgBox "Last Row: " & Last_Row End Sub Sub Method_3_Range_Find_Col() ' PURPOSE : Find last non-blank column on Active Sheet Dim Last_Col As Long On Error Resume Next Last_Col = Cells.Find(What:="*", _ After:=Range("A1"), _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious).Column On Error GoTo 0 MsgBox "Last Column: " & Last_Col End Sub
Download the sample file with VBA code:
Here’s the Microsoft reference for the Range.Find Method (Excel):
The Range.Find method is looking for the first non-blank cell (“*”). The asterisk represents a wildcard character that looks for any text or numbers in the cell.
Let’s see how it works for finding the last non-blank row.
It starts in cell A1, then moves backwards (xlPrevious) and starts to search from the last cell in the worksheet. It then moves right-to-left (xlByRows) and loops up through each row until it finds a non-blank cell. When a non-blank is found it stops and returns the row number.
The Range.Find method has 9 parameters but only the first parameter (What) is actually required by Excel for searching.
expression.Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)
Here it’s important for us to specify each of the first 6 parameters.
If you don’t specify the optional arguments for LookIn, LookAt, and SearchOrder then the Find method will use whatever options you used last in Excel’s Find Window.
This is because the settings for LookIn, LookAt, SearchOrder, and MatchByte are saved each time you use this method. If you don’t specify values for these arguments the next time you call the method, the saved values are used.
Setting these arguments also changes the settings in the Find dialog box, and changing the settings in the Find dialog box changes the saved values that are used if you omit the arguments.
So to avoid problems, set the optional arguments explicitly each time you use this method.
expression.Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection)
OK… with that out of the way, here’s an explanation for each parameter we use:
You can create a custom function (UDF) that uses the Range.Find method. Or you can use the UDF below that I wrote.
Once you have the UDF, you can copy it into any VBA project or code module, and use it to return the last row, column, or cell.
The UDF in the sample workbook optionally allows you to specify the worksheet and range to search.
If you don’t specify the worksheet, the UDF defaults to the ActiveSheet. If you don’t specify the range, the UDF defaults to all cells on the worksheet.
Function FindLast(SearchType As String, _ Optional Str_Worksheet As String, _ Optional Str_Range As String) ' PURPOSE : Find last row, column or cell in specified range ' ' PARAMETERS: ' SearchType ' Row = last row returned as Long ' Col = last column returned as Long ' Cell = last cell returned as String Dim Last_Row As Long Dim Last_Col As Long Dim Wks_Search As Worksheet Dim Rng_Search As Range On Error GoTo ErrHandler 'Default to ActiveSheet if Worksheet not specified If Str_Worksheet = "" Then Set Wks_Search = ActiveSheet Else Set Wks_Search = Worksheets(Str_Worksheet) End If 'Default to all cells if Range not specified If Str_Range = "" Then Set Rng_Search = Wks_Search.Cells Else Set Rng_Search = Wks_Search.Range(Str_Range) End If Select Case SearchType Case "Row", "row": 'Find last row with data On Error Resume Next FindLast = Rng_Search.Find(What:="*", _ After:=Rng_Search.Cells(1), _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious).Row On Error GoTo 0 Case "Col", "col": 'Find last column with data On Error Resume Next FindLast = Rng_Search.Find(What:="*", _ After:=Rng_Search.Cells(1), _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious).Column On Error GoTo 0 Case "Cell", "cell": 'Find last cell using last row and column On Error Resume Next Last_Row = Rng_Search.Find(What:="*", _ After:=Rng_Search.Cells(1), _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious).Row Last_Col = Rng_Search.Find(What:="*", _ After:=Rng_Search.Cells(1), _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious).Column FindLast = Wks_Search.Cells(Last_Row, Last_Col).Address(False, False) 'Return Address Row, Col as Relative References (not Absolute) 'If Last_Row or Last_Col = 0 then entire sheet is blank, return first cell If Err.Number > 0 Then FindLast = Rng_Search.Cells(1).Address(False, False) 'Return Address Row, Col as Relative References (not Absolute) Err.Clear End If On Error GoTo 0 End Select Exit Function ErrHandler: MsgBox "Error setting the worksheet or range." End Function
Download the sample file with VBA code:
The UDF FindLast(SearchType, Str_Worksheet, Str_Range) can be used inside your VBA Macros like in these examples:
Sub Last_Row_Example() Dim Last_Row As Long Dim Wks As Worksheet ' Find the last row Last_Row = FindLast("row", TARGET_WORKSHEET) MsgBox "Last Row: " & Last_Row ' Output debug information to Immediate Window Debug.Print "Worksheet "; Tab(21); ":"; Tab(23); _ TARGET_WORKSHEET & vbCr & _ "Last Row with Data : "; Tab(23); _ Last_Row & vbCr End Sub Sub Last_Col_Example() Dim Last_Col As Long Dim Wks As Worksheet ' Find the last row Last_Col = FindLast("Col", TARGET_WORKSHEET) MsgBox "Last Column: " & Columns(Last_Col).Address(False, False) '.Address(False, False) gives row/column as relative references ' Output debug information to Immediate Window Debug.Print "Worksheet "; Tab(21); ":"; Tab(23); _ TARGET_WORKSHEET & vbCr & _ "Last Col with Data : "; Tab(23); _ Last_Col & vbCr End Sub Sub Last_Cell_Example() Dim Last_Cell As String Dim Wks As Worksheet ' Find the last cell Last_Cell = FindLast("Cell", TARGET_WORKSHEET) MsgBox "Last Cell: " & Last_Cell ' Output debug information to Immediate Window Debug.Print "Worksheet "; Tab(21); ":"; Tab(23); _ TARGET_WORKSHEET & vbCr & _ "Last Cell with Data : "; Tab(23); _ Last_Cell & vbCr End Sub
The UDF can also be used on your worksheets directly like in this screenshot:
Please leave a comment below if you have any questions or need help finding the last cell in your worksheet. I’m happy to help!
Excel VBA Cheat Sheets » Find it hard to write Excel VBA Macros? Think there’s too much to remember? You’re not alone! Check out my convenient one-page guides. They’ll help you write Excel VBA Macros better and faster.
Click here to find out more…
Hi, I'm Victor!
Are you struggling with complex Excel tasks? Feeling overwhelmed by spreadsheets that are hard to use?
Many people believe mastering Excel is about learning shortcuts, functions, and formulas. But this overlooks the importance of building practical, real-world applications. It's not just about knowing the tools. It's about using them effectively.
That's where I come in. You'll get a unique perspective to Excel training from me. I have over 20 years of experience at Deloitte and two global tech companies. And I know what can make a difference in your career.
Let me help you integrate Excel into your professional life. Starting today. Read one of my articles, watch one of my videos. Then apply the new technique to your work. You'll see the difference immediately!
Recommended Posts
Discover the PROVEN Blueprint for transforming your Excel skills, supercharging your productivity, and standing out in your career! My course helps you to learn Excel VBA and save hours of time even if you have zero prior experience with programming.
Solve tricky Excel problems and take your work to the next level! Get customized solutions for your unique needs. Save time and gain insights with truly expert Excel solutions from only $97 per task.
Get a clear overview of your project progress using the Excel project timeline. Use it to communicate the big picture, track task progress, and stay on top of your project goals. Stay organized with our project timeline!
Our cheat sheets provide quick and easy reference to commonly used Excel VBA concepts and code snippets.
Unlock new levels of productivity and efficiency with our cheat sheets, and write VBA code like a pro in no time.
RECOMMENDED READING
Are you looking to upskill and stay ahead of the curve? Excel is a powerful tool that keeps growing in demand. We round up the best online courses for learning Excel.
Are you looking to up your spreadsheet game? Excel is an invaluable tool that can help you stay organized and save time. From data analysis to budgets, Excel can do it all!
Today, having Excel skills is more critical than ever. Those who know how to use Excel are more likely to find higher-paying jobs. And get promoted faster.