[excel] Error in finding last used cell in Excel with VBA

When I want to find the last used cell value, I use:

Dim LastRow As Long

LastRow = Range("E4:E48").End(xlDown).Row

Debug.Print LastRow

I'm getting the wrong output when I put a single element into a cell. But when I put more than one value into the cell, the output is correct. What's the reason behind this?

This question is related to excel vba excel-formula

The answer is


Sub lastRow()

    Dim i As Long
        i = Cells(Rows.Count, 1).End(xlUp).Row
            MsgBox i

End Sub

sub LastRow()

'Paste & for better understanding of the working use F8 Key to run the code .

dim WS as worksheet
dim i as long

set ws = thisworkbook("SheetName")

ws.activate

ws.range("a1").select

ws.range("a1048576").select

activecell.end(xlup).select

i= activecell.row

msgbox "My Last Row Is " & i

End sub

I was looking for a way to mimic the CTRL+Shift+End, so dotNET solution is great, except with my Excel 2010 I need to add a set if I want to avoid an error:

Function GetLastCell(sh As Worksheet) As Range
  Set GetLastCell = sh.Cells(1, 1).SpecialCells(xlLastCell)
End Function

and how to check this for yourself:

Sub test()
  Dim ws As Worksheet, r As Range
  Set ws = ActiveWorkbook.Sheets("Sheet1")
  Set r = GetLastCell(ws)
  MsgBox r.Column & "-" & r.Row
End Sub

Here's my two cents.

IMHO the risk of a hidden row with data being excluded is too significant to let xlUp be considered a One stop answer. I agree it's simple and will work MOST of the time, but it presents the risk of understating the last row, without any warning. This could produce CATASTROPHIC results at some poinit for someone who jumped on Stack Overlow and was looking to "sure way" to capture this value.

The Find method is flawless with respect to reliably pulling the last non-blank row and it would be my One Stop Answer. However the drawback of changing the Find settings can be annoying, particularly if this is part of a UDF.

The other answers posted are okay, however the complexity gets a little excessive. Thus here's my attempt to find a balance of reliability, minimal complexity, and not using Find.

Function LastRowNumber(Optional rng As Range) As Long

If rng Is Nothing Then
    Set rng = ActiveSheet.UsedRange
Else
    Set rng = Intersect(rng.Parent.UsedRange, rng.EntireColumn)
    If rng Is Nothing Then
        LastRowNumber = 1
        Exit Function
    ElseIf isE = 0 Then
        LastRowNumber = 1
        Exit Function
    
    End If

End If

LastRowNumber = rng.Cells(rng.Rows.Count, 1).Row

Do While IsEmpty(Intersect(rng, _
    rng.Parent.Rows(LastRowNumber)))

    LastRowNumber = LastRowNumber - 1
Loop

End Function

Why this is good:

  • Reasonably simple, not a lot of variables.
  • Allows for multiple columns.
  • Doesn't modify Find settings
  • Dynamic if used as a UDF with the entire column selected.

Why this is bad:

  • With very large sets of data and a massive gap between used range and last row in specified columns, this will perform slower, in rare cases SIGNIFICANTLY slower.

However, I think a One-Stop-Solution that has a drawback of messing up find settings or performing slower is a better overall solution. A user can then tinker with their settings to try to improve, knowing what's going on with their code. Using xLUp will not warn of the potential risks and they could carry on for who knows how long not knowing their code was not working correctly.


Note: this answer was motivated by this comment. The purpose of UsedRange is different from what is mentioned in the answer above.

As to the correct way of finding the last used cell, one has first to decide what is considered used, and then select a suitable method. I conceive at least three meanings:

  1. Used = non-blank, i.e., having data.

  2. Used = "... in use, meaning the section that contains data or formatting." As per official documentation, this is the criterion used by Excel at the time of saving. See also this official documentation. If one is not aware of this, the criterion may produce unexpected results, but it may also be intentionally exploited (less often, surely), e.g., to highlight or print specific regions, which may eventually have no data. And, of course, it is desirable as a criterion for the range to use when saving a workbook, lest losing part of one's work.

  3. Used = "... in use, meaning the section that contains data or formatting" or conditional formatting. Same as 2., but also including cells that are the target for any Conditional Formatting rule.

How to find the last used cell depends on what you want (your criterion).

For criterion 1, I suggest reading this answer. Note that UsedRange is cited as unreliable. I think that is misleading (i.e., "unfair" to UsedRange), as UsedRange is simply not meant to report the last cell containing data. So it should not be used in this case, as indicated in that answer. See also this comment.

For criterion 2, UsedRange is the most reliable option, as compared to other options also designed for this use. It even makes it unnecessary to save a workbook to make sure that the last cell is updated. Ctrl+End will go to a wrong cell prior to saving (“The last cell is not reset until you save the worksheet”, from http://msdn.microsoft.com/en-us/library/aa139976%28v=office.10%29.aspx. It is an old reference, but in this respect valid).

For criterion 3, I do not know any built-in method. Criterion 2 does not account for Conditional Formatting. One may have formatted cells, based on formulas, which are not detected by UsedRange or Ctrl+End. In the figure, the last cell is B3, since formatting was applied explicitly to it. Cells B6:D7 have a format derived from a Conditional Formatting rule, and this is not detected even by UsedRange. Accounting for this would require some VBA programming.

enter image description here


As to your specific question: What's the reason behind this?

Your code uses the first cell in your range E4:E48 as a trampoline, for jumping down with End(xlDown).

The "erroneous" output will obtain if there are no non-blank cells in your range other than perhaps the first. Then, you are leaping in the dark, i.e., down the worksheet (you should note the difference between blank and empty string!).

Note that:

  1. If your range contains non-contiguous non-blank cells, then it will also give a wrong result.

  2. If there is only one non-blank cell, but it is not the first one, your code will still give you the correct result.


Since the original question is about problems with finding the last cell, in this answer I will list the various ways you can get unexpected results; see my answer to "How can I find last row that contains data in the Excel sheet with a macro?" for my take on solving this.

I'll start by expanding on the answer by sancho.s and the comment by GlennFromIowa, adding even more detail:

[...] one has first to decide what is considered used. I see at least 6 meanings. Cell has:

  • 1) data, i.e., a formula, possibly resulting in a blank value;
  • 2) a value, i.e., a non-blank formula or constant;
  • 3) formatting;
  • 4) conditional formatting;
  • 5) a shape (including Comment) overlapping the cell;
  • 6) involvement in a Table (List Object).

Which combination do you want to test for? Some (such as Tables) may be more difficult to test for, and some may be rare (such as a shape outside of data range), but others may vary based on the situation (e.g., formulas with blank values).

Other things you might want to consider:

  • A) Can there be hidden rows (e.g. autofilter), blank cells or blank rows?
  • B) What kind of performance is acceptable?
  • C) Can the VBA macro affect the workbook or the application settings in any way?

With that in mind, let's see how the common ways of getting the "last cell" can produce unexpected results:

  • The .End(xlDown) code from the question will break most easily (e.g. with a single non-empty cell or when there are blank cells in between) for the reasons explained in the answer by Siddharth Rout here (search for "xlDown is equally unreliable.")
  • Any solution based on Counting (CountA or Cells*.Count) or .CurrentRegion will also break in presence of blank cells or rows
  • A solution involving .End(xlUp) to search backwards from the end of a column will, just as CTRL+UP, look for data (formulas producing a blank value are considered "data") in visible rows (so using it with autofilter enabled might produce incorrect results ??).

    You have to take care to avoid the standard pitfalls (for details I'll again refer to the answer by Siddharth Rout here, look for the "Find Last Row in a Column" section), such as hard-coding the last row (Range("A65536").End(xlUp)) instead of relying on sht.Rows.Count.

  • .SpecialCells(xlLastCell) is equivalent to CTRL+END, returning the bottom-most and right-most cell of the "used range", so all caveats that apply to relying on the "used range", apply to this method as well. In addition, the "used range" is only reset when saving the workbook and when accessing worksheet.UsedRange, so xlLastCell might produce stale results?? with unsaved modifications (e.g. after some rows were deleted). See the nearby answer by dotNET.
  • sht.UsedRange (described in detail in the answer by sancho.s here) considers both data and formatting (though not conditional formatting) and resets the "used range" of the worksheet, which may or may not be what you want.

    Note that a common mistake ?is to use .UsedRange.Rows.Count??, which returns the number of rows in the used range, not the last row number (they will be different if the first few rows are blank), for details see newguy's answer to How can I find last row that contains data in the Excel sheet with a macro?

  • .Find allows you to find the last row with any data (including formulas) or a non-blank value in any column. You can choose whether you're interested in formulas or values, but the catch is that it resets the defaults in the Excel's Find dialog ????, which can be highly confusing to your users. It also needs to be used carefully, see the answer by Siddharth Rout here (section "Find Last Row in a Sheet")
  • More explicit solutions that check individual Cells' in a loop are generally slower than re-using an Excel function (although can still be performant), but let you specify exactly what you want to find. See my solution based on UsedRange and VBA arrays to find the last cell with data in the given column -- it handles hidden rows, filters, blanks, does not modify the Find defaults and is quite performant.

Whatever solution you pick, be careful

  • to use Long instead of Integer to store the row numbers (to avoid getting Overflow with more than 65k rows) and
  • to always specify the worksheet you're working with (i.e. Dim ws As Worksheet ... ws.Range(...) instead of Range(...))
  • when using .Value (which is a Variant) avoid implicit casts like .Value <> "" as they will fail if the cell contains an error value.

One important note to keep in mind when using the solution ...

LastRow = ws.Cells.Find(What:="*", After:=ws.range("a1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

... is to ensure that your LastRow variable is of Long type:

Dim LastRow as Long

Otherwise you will end up getting OVERFLOW errors in certain situations in .XLSX workbooks

This is my encapsulated function that I drop in to various code uses.

Private Function FindLastRow(ws As Worksheet) As Long
    ' --------------------------------------------------------------------------------
    ' Find the last used Row on a Worksheet
    ' --------------------------------------------------------------------------------
    If WorksheetFunction.CountA(ws.Cells) > 0 Then
        ' Search for any entry, by searching backwards by Rows.
        FindLastRow = ws.Cells.Find(What:="*", After:=ws.range("a1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    End If
End Function

Find Last Row in a Column OR a Table Column(ListObject) by range

Finding the last row requires:

  1. Specifying several parameters (table name, column inside table relative to first column, worksheet, range).
  2. May require switching between methods. e.g. if the range is a Table (List Object) or not. Using the wrong search type will return wrong results.

This proposed solution is more general, requires only the range ,less chance of typos and is short (just calling MyLastRow function).

Sub test()
Dim rng As Range
Dim Result As Long
Set rng = Worksheets(1).Range("D4")
Result = MyLastRow(rng)
End Sub
    Function MyLastRow(FirstRow As Range) As Long
    Dim WS As Worksheet
    Dim TableName As String
    Dim ColNumber As Long
    Dim LastRow As Long
    Dim FirstColumnTable As Long
    Dim ColNumberTable As Long
    Set WS = FirstRow.Worksheet
    TableName = GetTableName(FirstRow)
    ColNumber = FirstRow.Column
    
    ''If the table (ListObject) does not start in column "A" we need to calculate the 
    ''first Column table and how many Columns from its beginning the Column is located.
    If TableName <> vbNullString Then
     FirstColumnTable = WS.ListObjects(TableName).ListColumns(1).Range.Column
     ColNumberTable = ColNumber - FirstColumnTable + 1
    End If 

    If TableName = vbNullString Then
    LastRow = WS.Cells(WS.Rows.Count, ColNumber).End(xlUp).Row
    Else
    LastRow = WS.ListObjects(TableName).ListColumns(ColNumberTable).Range.Find( _
               What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    End If
    MyLastRow = LastRow
    End Function
    
    ''Get Table Name by Cell Range
    Function GetTableName(CellRange As Range) As String
        If CellRange.ListObject Is Nothing Then
            GetTableName = vbNullString
        Else
            GetTableName = CellRange.ListObject.Name
        End If
    End Function

I created this one-stop function for determining the last row, column and cell, be it for data, formatted (grouped/commented/hidden) cells or conditional formatting.

Sub LastCellMsg()
    Dim strResult As String
    Dim lngDataRow As Long
    Dim lngDataCol As Long
    Dim strDataCell As String
    Dim strDataFormatRow As String
    Dim lngDataFormatCol As Long
    Dim strDataFormatCell As String
    Dim oFormatCond As FormatCondition
    Dim lngTempRow As Long
    Dim lngTempCol As Long
    Dim lngCFRow As Long
    Dim lngCFCol As Long
    Dim strCFCell As String
    Dim lngOverallRow As Long
    Dim lngOverallCol As Long
    Dim strOverallCell As String

    With ActiveSheet

        If .ListObjects.Count > 0 Then
            MsgBox "Cannot return reliable results, as there is at least one table in the worksheet."
            Exit Sub
        End If

        strResult = "Workbook name: " & .Parent.Name & vbCrLf
        strResult = strResult & "Sheet name: " & .Name & vbCrLf

        'DATA:
        'last data row
        If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
            lngDataRow = .Cells.Find(What:="*", _
             After:=.Range("A1"), _
             Lookat:=xlPart, _
             LookIn:=xlFormulas, _
             SearchOrder:=xlByRows, _
             SearchDirection:=xlPrevious, _
             MatchCase:=False).Row
        Else
            lngDataRow = 1
        End If
        'strResult = strResult & "Last data row: " & lngDataRow & vbCrLf

        'last data column
        If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
            lngDataCol = .Cells.Find(What:="*", _
             After:=.Range("A1"), _
             Lookat:=xlPart, _
             LookIn:=xlFormulas, _
             SearchOrder:=xlByColumns, _
             SearchDirection:=xlPrevious, _
             MatchCase:=False).Column
        Else
            lngDataCol = 1
        End If
        'strResult = strResult & "Last data column: " & lngDataCol & vbCrLf

        'last data cell
        strDataCell = Replace(Cells(lngDataRow, lngDataCol).Address, "$", vbNullString)
        strResult = strResult & "Last data cell: " & strDataCell & vbCrLf

        'FORMATS:
        'last data/formatted/grouped/commented/hidden row
        strDataFormatRow = StrReverse(Split(StrReverse(.UsedRange.Address), "$")(0))
        'strResult = strResult & "Last data/formatted row: " & strDataFormatRow & vbCrLf

        'last data/formatted/grouped/commented/hidden column
        lngDataFormatCol = Range(StrReverse(Split(StrReverse(.UsedRange.Address), "$")(1)) & "1").Column
        'strResult = strResult & "Last data/formatted column: " & lngDataFormatCol & vbCrLf

        'last data/formatted/grouped/commented/hidden cell
        strDataFormatCell = Replace(Cells(strDataFormatRow, lngDataFormatCol).Address, "$", vbNullString)
        strResult = strResult & "Last data/formatted cell: " & strDataFormatCell & vbCrLf

        'CONDITIONAL FORMATS:
        For Each oFormatCond In .Cells.FormatConditions

            'last conditionally-formatted row
            lngTempRow = CLng(StrReverse(Split(StrReverse(oFormatCond.AppliesTo.Address), "$")(0)))
            If lngTempRow > lngCFRow Then lngCFRow = lngTempRow

            'last conditionally-formatted column
            lngTempCol = Range(StrReverse(Split(StrReverse(oFormatCond.AppliesTo.Address), "$")(1)) & "1").Column
            If lngTempCol > lngCFCol Then lngCFCol = lngTempCol
        Next
        'no results are returned for Conditional Format if there is no such
        If lngCFRow <> 0 Then
            'strResult = strResult & "Last cond-formatted row: " & lngCFRow & vbCrLf
            'strResult = strResult & "Last cond-formatted column: " & lngCFCol & vbCrLf

            'last conditionally-formatted cell
            strCFCell = Replace(Cells(lngCFRow, lngCFCol).Address, "$", vbNullString)
            strResult = strResult & "Last cond-formatted cell: " & strCFCell & vbCrLf
        End If

        'OVERALL:
        lngOverallRow = Application.WorksheetFunction.Max(lngDataRow, strDataFormatRow, lngCFRow)
        'strResult = strResult & "Last overall row: " & lngOverallRow & vbCrLf
        lngOverallCol = Application.WorksheetFunction.Max(lngDataCol, lngDataFormatCol, lngCFCol)
        'strResult = strResult & "Last overall column: " & lngOverallCol & vbCrLf
        strOverallCell = Replace(.Cells(lngOverallRow, lngOverallCol).Address, "$", vbNullString)
        strResult = strResult & "Last overall cell: " & strOverallCell & vbCrLf

        MsgBox strResult
        Debug.Print strResult

    End With

End Sub

Results look like this:
determine last cell

For more detailed results, some lines in the code can be uncommented:
last column, row

One limitation exists - if there are tables in the sheet, results can become unreliable, so I decided to avoid running the code in this case:

If .ListObjects.Count > 0 Then
    MsgBox "Cannot return reliable results, as there is at least one table in the worksheet."
    Exit Sub
End If

However this question is seeking to find the last row using VBA, I think it would be good to include an array formula for worksheet function as this gets visited frequently:

{=ADDRESS(MATCH(INDEX(D:D,MAX(IF(D:D<>"",ROW(D:D)-ROW(D1)+1)),1),D:D,0),COLUMN(D:D))}

You need to enter the formula without brackets and then hit Shift + Ctrl + Enter to make it an array formula.

This will give you address of last used cell in the column D.


I wonder that nobody has mentioned this, But the easiest way of getting the last used cell is:

Function GetLastCell(sh as Worksheet) As Range
    GetLastCell = sh.Cells(1,1).SpecialCells(xlLastCell)
End Function

This essentially returns the same cell that you get by Ctrl + End after selecting Cell A1.

A word of caution: Excel keeps track of the most bottom-right cell that was ever used in a worksheet. So if for example you enter something in B3 and something else in H8 and then later on delete the contents of H8, pressing Ctrl + End will still take you to H8 cell. The above function will have the same behavior.


I would add to the answer given by Siddarth Rout to say that the CountA call can be skipped by having Find return a Range object, instead of a row number, and then test the returned Range object to see if it is Nothing (blank worksheet).

Also, I would have my version of any LastRow procedure return a zero for a blank worksheet, then I can know it is blank.


sub last_filled_cell()
msgbox range("A65536").end(xlup).row
end sub

Here, A65536 is the last cell in the Column A this code was tested on excel 2003.


For the last 3+ years these are the functions that I am using for finding last row and last column per defined column(for row) and row(for column):

Last Column:

Function lastCol(Optional wsName As String, Optional rowToCheck As Long = 1) As Long

    Dim ws  As Worksheet

    If wsName = vbNullString Then
        Set ws = ActiveSheet
    Else
        Set ws = Worksheets(wsName)
    End If

    lastCol = ws.Cells(rowToCheck, ws.Columns.Count).End(xlToLeft).Column

End Function

Last Row:

Function lastRow(Optional wsName As String, Optional columnToCheck As Long = 1) As Long

    Dim ws As Worksheet

    If wsName = vbNullString Then
        Set ws = ActiveSheet
    Else
        Set ws = Worksheets(wsName)
    End If

    lastRow = ws.Cells(ws.Rows.Count, columnToCheck).End(xlUp).Row

End Function

For the case of the OP, this is the way to get the last row in column E:

Debug.Print lastRow(columnToCheck:=Range("E4:E48").Column)

Last Row, counting empty rows with data:

Here we may use the well-known Excel formulas, which give us the last row of a worksheet in Excel, without involving VBA - =IFERROR(LOOKUP(2,1/(NOT(ISBLANK(A:A))),ROW(A:A)),0)

In order to put this in VBA and not to write anything in Excel, using the parameters for the latter functions, something like this could be in mind:

Public Function LastRowWithHidden(Optional wsName As String, Optional columnToCheck As Long = 1) As Long

    Dim ws As Worksheet

    If wsName = vbNullString Then
        Set ws = ActiveSheet
    Else
        Set ws = Worksheets(wsName)
    End If

    Dim letters As String
    letters = ColLettersGenerator(columnToCheck)
    LastRowWithHidden = ws.Evaluate("=IFERROR(LOOKUP(2,1/(NOT(ISBLANK(" & letters & "))),ROW(" & letters & " )),0)")

End Function

Function ColLettersGenerator(col As Long) As String

    Dim result As Variant
    result = Split(Cells(1, col).Address(True, False), "$")
    ColLettersGenerator = result(0) & ":" & result(0)

End Function

Examples related to excel

Python: Pandas pd.read_excel giving ImportError: Install xlrd >= 0.9.0 for Excel support Converting unix time into date-time via excel How to increment a letter N times per iteration and store in an array? 'Microsoft.ACE.OLEDB.16.0' provider is not registered on the local machine. (System.Data) How to import an Excel file into SQL Server? Copy filtered data to another sheet using VBA Better way to find last used row Could pandas use column as index? Check if a value is in an array or not with Excel VBA How to sort dates from Oldest to Newest in Excel?

Examples related to vba

Copy filtered data to another sheet using VBA Better way to find last used row Check if a value is in an array or not with Excel VBA Creating an Array from a Range in VBA Excel: macro to export worksheet as CSV file without leaving my current Excel sheet VBA: Convert Text to Number What's the difference between "end" and "exit sub" in VBA? Rename Excel Sheet with VBA Macro Extract Data from PDF and Add to Worksheet Quicker way to get all unique values of a column in VBA?

Examples related to excel-formula

Excel doesn't update value unless I hit Enter Referencing value in a closed Excel workbook using INDIRECT? Conditionally formatting cells if their value equals any value of another column Sum values from multiple rows using vlookup or index/match functions What does an exclamation mark before a cell reference mean? If two cells match, return value from third Excel - programm cells to change colour based on another cell Format numbers in thousands (K) in Excel Excel Formula which places date/time in cell when data is entered in another cell in the same row Excel formula to display ONLY month and year?