• If you would like to post, please check out the MrExcel Message Board FAQ and click here to register.
    If you forgot your password, you can reset your password.
  • Excel articles and downloadable files provided in the articles have not been reviewed by MrExcel Publishing. Please apply the provided methods / codes and open the files at your own risk.
    If you have any questions regarding an article, please use the Article Discussion section.
CephasOz

Determining worksheet data area

There are a few standard ways to find the area containing data in a worksheet, but each has a problem.

1) Activesheet.Cells(1).CurrentRegion won't work correctly if there are blank rows or columns
2) Activesheet.Cells(Activesheet.Rows.Count, 1).End(xlUp).Row won't work correctly if there is data in a cell in a greater numbered row but in a different column.
3) Activesheet.UsedRegion isn't dynamic - it gets updated when Excel is good and ready
4) Using Find by either rows or columns only might miss an outlier cell in a greater column (if searching by rows) or a greater row (if searching by columns).

The code below is, I think, "bullet-proof". It returns the cell that is at the intersection of the greatest row and the greatest column (or A1 if it's a brand new worksheet). When copying, traversing, or clearing data, using this function will ensure that nothing is missed.

VBA Code:
'Finds the last used cell in a worksheet.
Function LastUsedCell(wksToUse As Worksheet) As Range
    Dim dblRow As Double
    Dim dblCol As Double
    Dim rngFound As Range
    '
    Set LastUsedCell = wksToUse.Cells(1, 1)
    On Error GoTo Err_Exit
    '
    Set rngFound = wksToUse.Cells.Find(What:="*", _
        LookIn:=xlFormulas, _
        LookAt:=xlPart, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious, _
        MatchCase:=False)
    If (Not (rngFound Is Nothing)) Then
        dblRow = rngFound.Row
        Set rngFound = wksToUse.Cells.Find(What:="*", _
            LookIn:=xlFormulas, _
            LookAt:=xlPart, _
            SearchOrder:=xlByColumns, _
            SearchDirection:=xlPrevious, _
            MatchCase:=False)
        dblCol = rngFound.Column
        Set LastUsedCell = wksToUse.Cells(dblRow, dblCol)
    End If
Housekeeping:
    Set rngFound = Nothing
    Exit Function
Err_Exit:
    Err.Clear
    Resume Housekeeping
End Function
When using the function, if only the last row or the last column is needed, the function could be used directly:
VBA Code:
dblLastRow = LastUsedCell(Activesheet).Row
and if setting a range for the data, it could be set by:
VBA Code:
With Activesheet
    Set rngToUse = .Range(.Cells(1,1),LastUsedCell(Activesheet))
End With
and if you needed both row and column information, setting a range to the return value would give you that.
VBA Code:
Set rngLastCell = LastUsedCell(Activesheet)
For dblRow = 1 to rngLastCell.Row
    For dblCol = 1 to rngLastCell.Column
This would work in every situation, even for an empty worksheet, because the default value is cell A1 of the worksheet in question (and never Nothing). My reason for working on this is that something reliable was needed, something that would work regardless of the shape of the data, regardless of the vagaries of Excel, and regardless of whether the worksheet was protected.

Thoughts?
Excel Version
365, 2019, 2016, 2013, 2011, 2010, 2007
Author
CephasOz
Views
442
First release
Last update
Rating
0.00 star(s) 0 ratings

Some videos you may like

This Week's Hot Topics

Top