SchimmelDevNest

Programming

Find last used cell in Excel VBA

27 September 2026 · 9 min read

Find last used cell in Excel VBA

Navigating large datasets in Microsoft Excel often requires precise automation, and a crucial step in many VBA (Visual Basic for Applications) scripts is to accurately find the last used cell in Excel VBA. Whether you’re appending new data, formatting a range, or building dynamic charts, pinpointing the exact boundaries of your data is fundamental. Incorrectly identifying the last cell can lead to errors, overwriting existing data, or processing empty rows and columns, significantly hindering your script’s efficiency and reliability. This guide delves into various robust methods for identifying the true extent of your populated worksheets, ensuring your Excel VBA projects are both powerful and precise.

Understanding “Last Used Cell” in Excel VBA

The concept of the “last used cell” in Excel VBA can be more nuanced than it initially appears. Excel internally tracks what it considers the “last cell” based on the furthest row and column that has ever contained data or formatting. This internal tracking, often influenced by deleted data or formatting applied far down a sheet, can sometimes lead to a “ghost” last cell that is far beyond your actual data, causing performance issues in your macros. For VBA developers, accurately defining “last used” typically means the last cell containing actual values, formulas, or visible formatting within a contiguous range or the entire sheet.

Understanding this distinction is vital for writing efficient and error-free VBA code. Relying solely on Excel’s built-in SpecialCells(xlCellTypeLastCell) can sometimes lead to unexpected results, especially if rows or columns were previously used and then cleared. Therefore, VBA offers several approaches to determine the VBA last row and VBA last column, each with its own strengths and ideal use cases. Choosing the right method depends on your specific data structure and the level of precision required for your automation task.

For instance, if you are working with a table that has no blank rows or columns in the middle, methods like CurrentRegion can be exceptionally fast. However, if your data is sparse or has intentional gaps, more explicit methods that check each row or column from the bottom or right are necessary. This section lays the groundwork for exploring these methods, ensuring you can confidently identify the boundaries of your data for any Excel VBA operation, from simple data entry to complex data manipulation in Excel.

Methods to Find Last Used Row

Pinpointing the last occupied row is a common requirement in Excel VBA, and several reliable methods can achieve this, each suited for different scenarios. The most robust and widely used approach involves starting from the very bottom of the sheet and moving upwards until the first non-empty cell is found. This technique effectively ignores any “ghost” cells below your actual data, providing an accurate VBA last row value.

One highly effective method for finding the last row is by utilizing the End(xlUp) property. This command simulates pressing Ctrl + Up Arrow key, which navigates from a specified cell to the first non-empty cell above it. For example, to find the last used row in Column A, you would use Cells(Rows.Count, “A”).End(xlUp).Row. This code starts at the very last possible row in Column A (65,536 for .xls or 1,048,576 for .xlsx) and moves up until it hits a cell with content. This approach is highly reliable because it doesn’t care about blank cells above the last data point, only the presence of data from the bottom up.

Another common technique leverages the UsedRange property, which returns a Range object representing the smallest range that encompasses all used cells on a worksheet. To find the last row using this method, you can use ActiveSheet.UsedRange.Rows.Count + ActiveSheet.UsedRange.Row - 1. While often accurate, this method can sometimes be misled by residual formatting or previously entered data that has since been cleared, as Excel might still consider those cells “used.” For a more consistently accurate result, especially when dealing with potentially “dirty” worksheets, the End(xlUp) method is generally preferred for finding the true last data row. For instance, if your data is in column B, Range(“B” & Rows.Count).End(xlUp).Row will give you the last row number in that specific column.

Methods to Find Last Used Column

Just as finding the last used row is critical, determining the last occupied column is equally important for dynamic range manipulation and data processing in Excel VBA. Similar to rows, several techniques exist to accurately identify the VBA last column, catering to different data layouts and precision requirements. These methods typically involve navigating from the far-right side of the worksheet inwards until the first non-empty cell is encountered.

The most dependable method for finding the last used column is by employing the End(xlToLeft) property. This command mimics pressing Ctrl + Left Arrow key, moving from a specified cell to the first non-empty cell to its left. To find the last used column in a specific row, say Row 1, you would use Cells(1, Columns.Count).End(xlToLeft).Column. This code starts at the very last possible column in Row 1 (e.g., Column XFD for .xlsx files) and moves left until it encounters a cell containing data. This approach is highly effective because it disregards any empty cells to the right of your actual data, providing the precise last column number with content in that particular row.

Alternatively, the UsedRange property can also be applied to determine the last column. The syntax ActiveSheet.UsedRange.Columns.Count + ActiveSheet.UsedRange.Column - 1 will give you the last column number within the sheet’s used range. While convenient, remember that UsedRange can sometimes extend beyond your visible data due to lingering formatting or historical data entry, potentially leading to an overestimation of the actual last column. For ensuring accuracy in scenarios where ghost cells might exist, or when you need the last column of a specific row, the End(xlToLeft) method offers superior reliability. Using these methods to consistently find last used cell in Excel VBA ensures your scripts always operate on the correct data boundaries.

  1. Identify the Target Row: Determine which row you need to check for the last used column. If you’re looking for the overall last column on the sheet, Row 1 is often a good starting point, assuming headers are present.

  2. Apply End(xlToLeft): Use the syntax Cells(TargetRowNumber, Columns.Count).End(xlToLeft).Column. This will navigate from the last possible column in the TargetRowNumber to the first filled cell to its left.

  3. Store the Result: Assign the returned column number to a variable, for instance, Dim lastCol As Long: lastCol = Cells(1, Columns.Count).End(xlToLeft).Column.

  4. Utilize the Column Number: Now last Question & Answer :
    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?

    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