- Excel Version
- 365
- 2019
- 2016
- 2013
- 2011
- 2010
- 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.
When using the function, if only the last row or the last column is needed, the function could be used directly:
and if setting a range for the data, it could be set by:
and if you needed both row and column information, setting a range to the return value would give you that.
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?
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 FunctionWhen 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).Rowand 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 Withand 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.ColumnThis 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?
