Categories
cell excel excel-formula vba

Find last used cell in Excel VBA

192

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?

3

  • 1

    stackoverflow.com/questions/71180/…

    – M–

    Jul 7, 2017 at 14:22

  • 2

    @FreeSoftwareServers I disagree – as there are many answers showing how to find the last row, this specific question is unique and specific in that it’s asking why a particular method does not work, a method suggested in a variety of other “how to” answers.

    Sep 24, 2021 at 14:51

  • I believe my below answer offers the most ideal one-stop solution. I’m open to criticism, but with new filter/query tools, I’m at least confident enough to post this comment and face the wrath of criticism for a rather brazen claim…

    Dec 27, 2021 at 21:21

347

NOTE: I intend to make this a “one stop post” where you can use the Correct way to find the last row. This will also cover the best practices to follow when finding the last row. And hence I will keep on updating it whenever I come across a new scenario/information.


Unreliable ways of finding the last row

Some of the most common ways of finding last row which are highly unreliable and hence should never be used.

  1. UsedRange
  2. xlDown
  3. CountA

UsedRange should NEVER be used to find the last cell which has data. It is highly unreliable. Try this experiment.

Type something in cell A5. Now when you calculate the last row with any of the methods given below, it will give you 5. Now color the cell A10 red. If you now use the any of the below code, you will still get 5. If you use Usedrange.Rows.Count what do you get? It won’t be 5.

Here is a scenario to show how UsedRange works.

enter image description here

xlDown is equally unreliable.

Consider this code

lastrow = Range("A1").End(xlDown).Row

What would happen if there was only one cell (A1) which had data? You will end up reaching the last row in the worksheet! It’s like selecting cell A1 and then pressing End key and then pressing Down Arrow key. This will also give you unreliable results if there are blank cells in a range.

CountA is also unreliable because it will give you incorrect result if there are blank cells in between.

And hence one should avoid the use of UsedRange, xlDown and CountA to find the last cell.


Find Last Row in a Column

To find the last Row in Col E use this

With Sheets("Sheet1")
    LastRow = .Range("E" & .Rows.Count).End(xlUp).Row
End With

If you notice that we have a . before Rows.Count. We often chose to ignore that. See THIS question on the possible error that you may get. I always advise using . before Rows.Count and Columns.Count. That question is a classic scenario where the code will fail because the Rows.Count returns 65536 for Excel 2003 and earlier and 1048576 for Excel 2007 and later. Similarly Columns.Count returns 256 and 16384, respectively.

The above fact that Excel 2007+ has 1048576 rows also emphasizes on the fact that we should always declare the variable which will hold the row value as Long instead of Integer else you will get an Overflow error.

Note that this approach will skip any hidden rows. Looking back at my screenshot above for column A, if row 8 were hidden, this approach would return 5 instead of 8.


Find Last Row in a Sheet

To find the Effective last row in the sheet, use this. Notice the use of Application.WorksheetFunction.CountA(.Cells). This is required because if there are no cells with data in the worksheet then .Find will give you Run Time Error 91: Object Variable or With block variable not set

With Sheets("Sheet1")
    If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
        lastrow = .Cells.Find(What:="*", _
                      After:=.Range("A1"), _
                      Lookat:=xlPart, _
                      LookIn:=xlFormulas, _
                      SearchOrder:=xlByRows, _
                      SearchDirection:=xlPrevious, _
                      MatchCase:=False).Row
    Else
        lastrow = 1
    End If
End With

Find Last Row in a Table (ListObject)

The same principles apply, for example to get the last row in the third column of a table:

Sub FindLastRowInExcelTableColAandB()
Dim lastRow As Long
Dim ws As Worksheet, tbl as ListObject
Set ws = Sheets("Sheet1")  'Modify as needed
'Assuming the name of the table is "Table1", modify as needed
Set tbl = ws.ListObjects("Table1")

With tbl.ListColumns(3).Range
    lastrow = .Find(What:="*", _
                After:=.Cells(1), _
                Lookat:=xlPart, _
                LookIn:=xlFormulas, _
                SearchOrder:=xlByRows, _
                SearchDirection:=xlPrevious, _
                MatchCase:=False).Row
End With

End Sub

24

  • 9

    @phan: Type something in cell A5. Now when you calculate the last row with any of the methods given above, it will give you 5. Now color the cell A10 red. If you now use the any of the above code, you will still get 5. If you use Usedrange.Rows.Count what do you get? It won’t be 5. Usedrange is highly unreliable to find the last row.

    Aug 13, 2012 at 19:48


  • 6

    Do note that .Find unfortunately messes up the user’s settings in the Find dialog – i.e. Excel only has 1 set of settings for the dialog, and you using .Find replaces them. Another trick is to still use UsedRange, but use it as an absolute (but unreliable) maximum from which you determine the correct maximum.

    Jan 31, 2014 at 12:34

  • 4

    @CarlColijn: I wouldn’t call it messing. 🙂 Excel simply remembers the last setting. Even when you manually do a Find, it remembers the last setting which in fact is a boon if one knows this “fact”

    Jan 31, 2014 at 12:37

  • 3

    @KeithPark: Please go ahead 🙂 Knowledge only has a meaning if it is spread 🙂

    Aug 29, 2014 at 7:11

  • 12

    I think that your description of UsedRange (it is highly unreliable to find the last cell which has data) is misleading. UsedRange is simply not intended for that purpose, even though in some cases it may give the correct result. I think that the experiment proposed adds to the confusion. The result obtained with UsedRange ($A$1:$A$8) does not depend on first entering data an deleting it. The figure on the right will still be the same even without having entered data an deleted it. Please see my answer.

    Dec 24, 2014 at 13:52

38

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

2

  • 5

    I agree that 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).

    Apr 12, 2017 at 18:42

  • I agree @Siddharth Rout’s answer is misleading (at best). UsedRange and it’s counterpart [in this context] SpecialCells(xlCellTypeLastCell) are both very reliable and extremely useful. And, as pointed out here, if you’re actual looking for the last used row, in 9 out of 10 cases, it’s the most efficient and reliable method. The key is knowing what it means and how and when to leverage it’s power.

    – Spinner

    Nov 11, 2021 at 5:49

21

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

1

  • 2

    @franklin – I’ve just noticed an inbox message with your correction which was rejected by reviewers. I corrected that mistake. I already used this function once when I needed and I will use it again, so really, huge thanks, my friend!

    – ZygD

    Feb 9, 2016 at 4:36