SHOP NOW

Excel VBA Tutorial: Find the Last Cell, Row or Column on an Excel Worksheet

Last updated on June 12, 2018 By

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:

How to choose the best way for you

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:

  • Option 1 – Range.SpecialCells : The simplest way to find the last used cell. Note that the last used cell can be different from the last non-blank cell. Just changing the font or fill color of a cell will turn it into a used cell – even if there’s no data in it.
  • Option 2 – Range.End : The Range.End way does the same thing as pressing the CTRL + Arrow Key keyboard shortcut. In VBA we can use this way to find the last non-blank cell in a single row or column.
  • Option 3 – Range.Find : The Range.Find way is a versatile way to find the last cell, row or column. Range.Find has a lot of VBA parameters but don’t let that put you off… it isn’t so scary when you understand it’s just a way to automate the Find and Replace dialog box (CTRL + F) in Excel.
  • Option 4 – User Defined Function : Finally you can create a custom function (UDF) with the Range.Find method. You can copy the UDF into any VBA project or code module, and use it to return the last row, column, or cell. This is the most complex and powerful way of finding the last cell, row or column that we cover in this article.

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…

#1 – Range.SpecialCells(xlCellTypeLastCell)

The SpecialCells method does the same thing as pressing the Ctrl+End keyboard shortcut and selects the last used cell on the sheet.

SpecialCells(xlCellTypeLastCell) VBA Code

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):

How the Range.SpecialCells method works

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.

Pros of Range.SpecialCells

  • You can find “used” rows and columns at the end of a worksheet and delete them.
  • You can compare the result of Range.SpecialCells with the result of Range.Find for non-blank cells to quickly find out if any unused rows or columns exist.
  • You can delete “used” rows/columns with no data to reduce file size and expand your scroll bar.

Cons of Range.SpecialCells

  • It finds the last used cell and NOT the last non-blank cell.
  • Excel normally only resets the last used cell when the workbook is saved. If the user or macro deletes the contents of some cells, then this method will not find the actual last used cell until after the file is saved. (you can get round this by forcing a refresh e.g. Activesheet.UsedRange)

#2 – Range.End

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.

Cons of Range.SpecialCells

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):

How the Range.End Property works

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.

Pros of Range.End

  • Range.End is simple to use and understand since it works the same way as the CTRL + Arrow Key shortcuts. If you haven’t tried these shortcuts, spend a couple of seconds testing them now. You’ll be glad you did!
  • Range.End can be used to find the first blank cell, or the last non-blank cell in a single row or column. To find the first blank cell in a column, just start at the top of the column and use (xlDown). The next cell down is the first blank cell.

Cons of Range.End

  • Range.End only works on a single row or column. If you have a data set with blanks in the last row or column, then it’s hit or miss whether this technique works. You’d have to decide which row or column to perform the method on.
  • If you want to find the last non-blank cell on the worksheet then you have to evaluate two statements. First to find the last row and second to find the last column. You can then combine these to reference the last cell.

#3 – Range.Find

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)

Range.Find VBA Code

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):

How the Range.Find method works

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:

  • What:=”*” – The asterisk is a wildcard character that looks for any text or number in the cell. It’s basically the same as searching for a non-blank cell.
  • After:=Range(“A1”) – Starts the search after cell A1, the first cell in the sheet. This means that A1 will NOT be searched. The next cell it searches depends on the SearchOrder and SearchDirection. This parameter can be changed to start in a different cell.
  • LookIn:=xlFormulas – This is important… it tells Range.Find to look in the formulas not just the values. The other option is xlValues, which only searches the values. If you use formulas to return blanks (=IF(A10>7,”OK”,””) then you can use xlValues to treat “” as blank cells.
  • LookAt:=xlPart – This parameter gets Excel to look at any part of the text inside the cell. The other option is xlWhole, which only matches the entire cell contents.
  • SearchOrder:=xlByRows – This tells Range.Find to search through each full row before moving to the next row. The other option here is xlByColumns, which is used to find the last column.
  • SearchDirection:=xlPrevious – This tells Range.Find which direction to search. xlPrevious means search from the right-to-left or bottom-to-top. The other option is xlNext, which moves from left-to-right or top-to-bottom.

Pros of Range.Find

  • Range.Find searches an entire range for the last non-blank row or column. It’s not limited to a single row or column.
  • The last row in a data set can have blanks and Range.Find will still find the last row.
  • The parameters of Range. Find can be changed to search in different directions and for specific values, not just blank cells.

Cons of Range.Find

  • There’s more VBA to write compared with techniques #1 and #2 (which can be written on one line of code)
  • Finding the last cell requires two statements. One to find the last row and one to find the last column. You then need to combine these to find the last cell.

#4 – User Defined Function

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.

Custom Function FindLast() VBA Code

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:

Pros of User Defined Function

  • You can check the whole worksheet or just a range – just specify the worksheet and/or range in the parameters.
  • You can use it on the worksheet in a cell just like a built-in Excel function.

Cons of User Defined Function

  • The VBA is significantly more complex than techniques #1, #2 and #3 because the UDF combines Range.Find for rows, columns and cells.

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…

Related Posts:

Author: Victor Chan

Victor expertly teaches Microsoft Excel to people all over the world. He has millions of views of his popular Excel explainer videos on YouTube. These show time-saving shortcuts and real-world applications explained with easy-to-follow visuals.

Victor has over 20 years of experience using Excel as a professional for Big 4 Audit Firm Deloitte and two global tech companies. He knows firsthand that being more productive with Excel can lead to greater job satisfaction and career growth.

Connect on YouTube, LinkedIn, Twitter.

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.

Learn VBA

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 help now!

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!

Get it here

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.

Learn More

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.

Find out...

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!

Read on

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.

Show me!

JOIN FREE EMAIL NEWSLETTER

Step up your Excel game! Join our free email newsletter and get updates on how to become more awesome at Excel.