How to find the last used cell (row or column) in Excel

I have encountered many spreadsheets. Some have been quite elegant. Most were Spartan yet useful. And not-insignificant number were downright dreadful and incomprehensible. The elegant ones are delight to code against. They have structure and order. The dreadful ones are difficult to code against due to their unpredictable structure. Yet, these ugly spreadsheets are the very ones that test our mettle as Excel add-in developers.

I discuss these spreadsheet “profiles” because, today, I want to show some strategies for finding the last used cell in an Excel worksheet or range. Given this post belongs to the Beginning Excel Development series… I will keep it clean and work with a spreadsheet that looks just like this:

I went ahead and named this range “Sample”. I will refer to it throughout the code sample. It’s Spartan… but it is useful for today’s topic.

So, what do I mean by “last used cell”? I mean the last cell in a row or column that is used in one way or another. Meaning, it isn’t empty. It has a value, a formula, formatting, a comment, etc.

Some of you out there are already saying something like, “oh, oh, I know, I know… use the UsedRange property!”

Let’s consider it.

The UsedRange object

Taking a quick look at the MSDN reference material, UsedRange is a property of the Worksheet object. If you access it ask it to tell you its secrets, it will give you a Range object. This range will contain all the cells in the worksheet that have ever contained a value.

This means if a cell doesn’t contain a value now but did a few moments ago, UsedRange will gladly serve it up to you. It will keep this secret to itself and leave it to you to read the documentation. Thus, UsedRange can be unpredictable. Some developers go as far as to call it unreliable.

Fine, so what are some reliable methods for finding the last used cell in an Excel worksheet or a range? It depends on what you mean by the last cell? The following code sample show my ideas for how to tackle the following scenarios:

• Find the last cell that contains data in a row or column
• Find the last cell that contains data in a range
• Find the last cell that contains a formula
• Find the last cell that contains data and has a comment
• Find the last cell that is empty but contains a comment
• Find the last cell that is empty but formatted
• Find the last hidden cell

I like to take these things one-at-a-time.

Find the last cell that contains data in a row or column

When I say “contains data”, I mean the cell does not contain an empty string. It has something in it that is visible to the user. To a user, an empty string in a cell is an empty cell.

These are words to live by.

We’ll start simple, with a procedure named MoveToLastCellWithData. All it does is mimic the CTL+ARROW keyboard combination in Excel.

Private Sub MoveToLastCellWithData(currentCell As Excel.Range, direction As Excel.XlDirection)
    currentCell.End(direction).Select()
End Sub

The procedure accepts an XlDirection parameter and then moves the cell selection in that direction. Below is how it can be utilized in a button click event.

Private Sub MoveToRight_OnClick(sender As Object, _
    control As IRibbonControl, pressed As Boolean) Handles MoveToRight.OnClick
 
    Dim rg As Excel.Range = ExcelApp.ActiveCell
    MoveToLastCellWithData(rg, Excel.XlDirection.xlToRight)
    Marshal.ReleaseComObject(rg)
End Sub

Here, I create a range reference to the active cell. I then pass it on to MoveToLastCellWithData so that good things will happen.

Find the last cell that contains data in an Excel range

For the few samples, I want to utilize the SpecialCells collection. This collection resides under the Range object and lets you reference several types of special cells. I decided to create procedure that will let me pass a range along with the type of special cells I want. The procedures assumes I am asking it to tell me (by way of selecting it) which cell in the range is the last of this type.

Here is MoveToLastCell.

Private Sub MoveToLastCell(cellType As Excel.XlCellType, _
    sheet As Excel.Worksheet, rangeName As String)
 
    Dim cells As Object = Nothing
    Dim lastCell As Excel.Range = Nothing
    Try
        cells = sheet.Range(rangeName).SpecialCells(cellType).Areas
 
        lastCell = cells(cells.Count)
        lastCell.Select()
 
    Catch
    End Try
 
    If lastCell IsNot Nothing Then Marshal.ReleaseComObject(lastCell)
    If cells IsNot Nothing Then Marshal.ReleaseComObject(cells)
End Sub

The method access the special cells and stuffs them into a range (cells). With some tricky math, it then selects that last cell.

Here is how to call it and tell it to find the last cell that contains data in the Sample range:

MoveToLastCell(Excel.XlCellType.xlCellTypeLastCell, ExcelApp.ActiveSheet, "Sample")

Boom!

Find the last cell that contains a formula

And here is how to make the same call but find the last cell containing an Excel formula.

MoveToLastCell(Excel.XlCellType.xlCellTypeFormulas, ExcelApp.ActiveSheet, "Sample")

Pretty cool, eh?

Find the last cell that contains data and has a comment

One last example with MoveToLastCell. This one finds the last cell that has data and a comment.

MoveToLastCell(Excel.XlCellType.xlCellTypeComments, ExcelApp.ActiveSheet, "Sample")

How about that, huh?

Find the last cell that is empty but contains a comment

Not impressed, let’s look at other empty cells. How about an empty cell that contains a comment? The MoveToLastEmptyCellbyType is the procedure we need. It is meant to do for empty cells what MoveToLastCell does for data-filled cells.

Private Sub MoveToLastEmptyCellByType(cellType As Excel.XlCellType, _
    sheet As Excel.Worksheet, rangeName As String)
 
    Dim cells As Object
    cells = sheet.Range(rangeName).SpecialCells(cellType).Areas
 
    Dim lastCell As Excel.Range = Nothing
 
    For i = 1 To cells.Count
        Dim cell As Excel.Range = cells(i)
        If cell.Value2 = vbNullString or VBEmpty Then
            lastCell = cell
            lastCell.Select()
        End If
        Marshal.ReleaseComObject(cell)
    Next
 
    'Check and return the last cell if still Nothing
    If lastCell Is Nothing Then
        lastCell = cells(cells.Count)
    End If
    If lastCell isnot Nothing then Marshal.ReleaseComObject(lastCell)
    If cells IsNot Nothing Then Marshal.ReleaseComObject(cells)
End Sub

This should look familiar but the difference is we need to loop through the cells to check for the existence of a VBNullString or VBEmpty value. If we find it, we reference the cell and keep looping. Eventually the loop runs out and the last cell referenced is the last cell we are looking for.

A call to MoveToLastEmptyCellbyType looks like these two examples.

MoveToLastEmptyCellbyType(Excel.XlCellType.xlCellTypeComments, ExcelApp.ActiveSheet, "Sample")
MoveToLastEmptyCellbyType(Excel.XlCellType.xlCellTypeFormulas, ExcelApp.ActiveSheet, "Sample")

Find the last cell that is empty but formatted

Because we are friends, I’ll admit that this one was a slight pain to figure out and implementing it in an Excel solution will be highly variable. I chose to keep it very simple by only searching for cells containing bold formatting. LastEmptyButFormattedCell begins by setting find format to bold. It then references the Sample range then starts a search using the find format.

Private Sub LastEmptyButFormattedCell()
    ExcelApp.FindFormat.Font.FontStyle = "Bold"
 
    Dim sheet As Excel.Worksheet = ExcelApp.ActiveSheet
    Dim found As Object = Nothing
    Dim loopCount As Integer = 0
    Dim firstCellAddress As String = ""
    Dim exitLoop As Boolean = False
    Dim emptyFound As Boolean = False
 
    With sheet.Range("Sample")
        'Select the range...
        .Select()
        'Ensure we are in the top-right cell
        .Cells(1, 1).Activate()
 
        Do
            'Do we need to exit the DO...LOOP?
            If exitLoop Then Exit Do
 
            'Peform a search
            found = .Find("", ExcelApp.ActiveCell, Excel.XlFindLookIn.xlValues, _
                Excel.XlLookAt.xlPart, Excel.XlSearchOrder.xlByRows, _
                Excel.XlSearchDirection.xlPrevious, MatchCase:=False, _
                SearchFormat:=True)
 
            'Check that we found a cell and activate it
            If Not found Is Nothing Then found.Activate()
 
            Select Case loopCount
                'The first time through the loop
                Case 0
                    'grab the address of this initial cell.
                    firstCellAddress = found.Address
                    loopCount = 1
                Case 1 'All subsequent loops
                    'if we are back at the first cell,
                    'we need to get out of the loop.
                    If firstCellAddress = found.Address Then
                        exitLoop = True
                    End If
                Case Else
            End Select
 
            If found.Value = vbEmpty Then
                'We found a formatted cell with an empty value...time to leave.
                emptyFound = True
                exitLoop = True
            End If
        Loop
 
        'We found, so select it to show the user.
        If emptyFound Then
            found.Select()
        Else
            MsgBox("No empty empty formatted cells exist.")
        End If
    End With
 
    If found IsNot Nothing Then Marshal.ReleaseComObject(found)
    Marshal.ReleaseComObject(sheet)
End Sub

I want to point a few things:

1. I coded this much like I would a VBA procedure.
2. The loop will run forever if not for the check of exitLoop. I did it this way because of point #1.
3. After referencing the sample range, the code moves the selection to the first cell (i.e. top-right) in the range. This is key because…
4. The search’s direction is xlPrevious (i.e. backwards).
5. After the first search, I store the address of the first cell. If we return to this cell in subsequent loops, we know we didn’t find any empty but formatted cells. So we need to exit the loop.
6. If we find a cell that matches the search, we need to check its value for an empty string.
7. If we find one, we select it and end the procedure. If we don’t fine one, we tell the user.

There might be a more elegant way to achieve the same thing. But this one tests out.

Find the last hidden cell in Excel

Finding the visible cells is easy using the SpecialCells and the MoveToLastCell method above:

MoveToLastCell(Excel.XlCellType.xlCellTypeVisible, ExcelApp.ActiveSheet, "Sample")

But finding hidden cells is a different matter because no SpecialCell constant exists for hidden cells. Instead, we need to rummage around the sample range and consider the row and column of each cell. If either the row or the column is hidden, then we now that cell is hidden.

Private Sub ShowLastHiddenCell()
    Dim foundCells As Excel.Range
    Dim sheet As Excel.Worksheet = ExcelApp.ActiveSheet
 
    foundCells = sheet.Range("sample")
 
    If Not foundCells Is Nothing Then
        'Loop through the cells and find the last one in the range that is hidden.
        Dim lastCell As Excel.Range = Nothing
        Dim address As String = Nothing
        For i = 1 To foundCells.Count
            Dim cell As Excel.Range = foundCells(i)
            If cell.EntireColumn.Hidden = True Or cell.EntireRow.Hidden = True Then
                'You are forgiven for thinking you can check Cell.Hidden
                lastCell = cell
                address = lastCell.Address
            End If
            Marshal.ReleaseComObject(cell)
        Next
 
        'Check and return the last cell if still Nothing
        If lastCell Is Nothing Then
            MsgBox("No hidden cells found.")
        Else
            MsgBox("The last hidden cell resides in " & address)
        End If
    End If
  Marshal.ReleaseComObject(foundCells)
End Sub

The ShowLastHiddenCell procedure loops through all cells. If any cells were hidden, it will show the address of the very last hidden cell it encountered.

What about performance when dealing with large worksheets or ranges?

This is always a limiting factor. I intentionally used a sample named range to keep from searching every cell in a worksheet. The more cells I’m searching the slower my add-in will perform. Thus, you need to implement strategies to ensure you are working only with a range of cells that makes sense for your scenario. If need to scan the entire worksheet, so be it… just display a “working on it” icon of your choosing so your user knows you are, uh, working on it.

*****

It sure would be easier if the Excel object model allowed us to do this in straight-forward manner. But, if Microsoft did that, what would we do with ourselves?

Available downloads:

This sample Excel add-in was developed using Add-in Express for Office and .net:

Find Last Cell Excel Add-in (VB.NET)

You may also be interested in:

• Creating COM add-ins for Excel 2013 – 2007. Customizing Excel Ribbon
• Excel add-in development in Visual Studio