• If you would like to post, please check out the MrExcel Message Board FAQ and register here. 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

Excel Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2011
  6. 2010
  7. 2007
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)
    '
    ' Turn off filtering
    On Error Resume Next
    wksToUse.ShowAllData
    '
    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?
Author
CephasOz
Views
2,449
First release
Last update

Ratings

0.00 star(s) 0 ratings

More Excel articles from CephasOz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top