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")
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:
- I coded this much like I would a VBA procedure.
- The loop will run forever if not for the check of exitLoop. I did it this way because of point #1.
- 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…
- The search’s direction is xlPrevious (i.e. backwards).
- 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.
- If we find a cell that matches the search, we need to check its value for an empty string.
- 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?
This sample Excel add-in was developed using Add-in Express for Office and .net: