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

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:
[Image] Download WorkbookVBA_Find_Last_Cell_Row_Column.xlsm (114Kb)

 

[IMAGE - Find last cell, row or column in Excel using VBA]

 

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.

[IMAGE - Use Range.SpecialCells to find last used cell]

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:
[Image] Download WorkbookVBA_Find_Last_Cell_Row_Column.xlsm (114Kb)

 

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)

» Back to contents

#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.

[IMAGE - Use Range.End Property to find last cell in a single row or column]

Range.End VBA Code

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:
[Image] Download WorkbookVBA_Find_Last_Cell_Row_Column.xlsm (114Kb)

 

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.

» Back to contents

#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.

[IMAGE - Use Range.Find method to find last non-blank row or column on Excel 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)

[IMAGE - Find and Replace Dialog Box]

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:

[Image] Download WorkbookVBA_Find_Last_Cell_Row_Column.xlsm (114Kb)

 

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.

[IMAGE - Use Range.Find method to find last non-blank row or column on Excel worksheet]

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.

» Back to contents

#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.

[IMAGE - Use custom User Defined Function to find Last Cell, Row, Column on Excel 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:

[Image] Download WorkbookVBA_Find_Last_Cell_Row_Column.xlsm (114Kb)

 

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:

[IMAGE - Use FindLast UDF function on worksheet to find last cell, row or column]

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.

» Back to contents

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…
Victor Chan
Victor has used Excel a lot since 2002. He's a Chartered Accountant (Fellow of the ICAEW) with MEng in Manufacturing Engineering from the University of Cambridge. He's on a mission to help you master Excel and VBA!
Join over 9,000 subscribers

GET BETTER AT EXCEL

Recent Posts

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Contact Us

Please send us an email and we'll get back to you, asap.

Not readable? Change text.