Creating a VBA Function to Find the Last Used Cell in a Column

Part II - Using VBA to Put it all Together

Overcoming these Problems
The first two problems can be avoided by using the IsEmpty function.

The result of (1) can be tested using this function. If the result is True then the returned cell is empty - possibly because the entire column is empty. However, this possibility needs to be considered in conjunction with the possibility that cell A65536 is the only non-empty cell. Again, this can be tested by using the IsEmpty function.

The problem of hidden cells isn't so easy to resolve. One solution is to unhide all cells prior to using (1). Unfortunately this approach isn’t practical because we cannot unhide rows and columns from within a user defined function. In any event this approach would change the spreadsheet's appearance (which is unlikely to be acceptable to most users). Another possible solution is to check the cells below the cell returned by (1) to see if there are any non-empty cells. The worksheet function CountA is useful for this purpose. If CountA detects a non-empty cell then we can loop through the lower cells until we find the actual last used cell. This approach is illustrated in the next section. (There are more efficient ways of finding hidden non-empty cells but we won't be discussing them here.).

Putting it all Together
The following code returns the row number of the last used cell in a given column on the active sheet. The result is 0 if the column is empty.

Const BottomRowNum = 65536

Function GetLastUsedCell(ColNum As Long) As Long
  Dim LastUsedCell As Long
  Dim UsedCellCount As Long
  Dim LowerCellRange As Range
  Dim CurrentCell As Range

  ' Ensure function recalculates each time worksheet is updated

  Application.Volatile
  With ActiveSheet

  ' Check that bottom cell is not empty

  If Not IsEmpty(.Cells(BottomRowNum, ColNum)) Then
    GetLastUsedCell = BottomRowNum
    Exit Function
  End If

  ' Estimate position of last used cell

  LastUsedCell = .Cells(BottomRowNum, ColNum).End(xlUp).Row
  If LastUsedCell = 1 Then

    ' Check cell as it may be empty. If so, return 0.

    If IsEmpty(.Cells(1, ColNum)) Then
      GetLastUsedCell = 0
      Exit Function
    End If
  End If

  Set LowerCellRange = Intersect(.UsedRange, _
  .Range(.Cells(LastUsedCell + 1, _
  ColNum), .Cells(BottomRowNum, ColNum)))

  If Not LowerCellRange Is Nothing Then

    ' Check for hidden non-empty cells

    UsedCellCount = _
    Application.WorksheetFunction.CountA(LowerCellRange)
    If UsedCellCount > 0 Then
      Set CurrentCell = .Cells(LastUsedCell + _
        LowerCellRange.Rows.Count, ColNum)

        ' Check vertically from bottom (of LowerCellRange)
        ' until first hidden non-empty cell is found

        While IsEmpty(CurrentCell)
          Set CurrentCell = CurrentCell.Offset(-1, 0)
        Wend
      LastUsedCell = CurrentCell.Row
      Set CurrentCell = Nothing
    End If
    Set LowerCellRange = Nothing
  End If
  End With

  GetLastUsedCell = LastUsedCell
End Function

(Download the last used cell vba code).

Although the code is annotated, there are one or two points that require further explanation.

We have used activesheet.cells(.,.).end(.) rather than activesheet.range(.).end(.) because this is a more convenient approach in the current context.

We need only look for hidden non-empty cells if UsedCellCount is positive. Note that we only check those cells below LastUsedCell that are in the used range. It is not necessary to check those cells below the used range since they must be empty.

Example
Using the above function is straightforward. First, insert the above code into a VBA module (see screenshot - 20Kb). Next, choose any cell on the active sheet and type in "=GetLastUsedCell(1)" and press the enter key (see screenshot - 7Kb). In this case, we have passed a value of 1 to the function. This means the function will return the last used cell in column A. The result will be 0 if column A is empty, 1 if the last used cell is A1, 2 if the last used cell is A2 etc. If we want to find the last used cell in column B then we would use 2 instead of 1. Similarly, we would use 3 if we are interested in column C and so on.

Email this article to a friend

Write to the editor

UltraSleuth Compare, check, analyze and document your Excel projects with UltraSleuth. Gain confidence in your spreadsheet results Speed up your spreadsheet development Understand third party spreadsheets Create an audit trail  Learn More...