Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
How do I find the REAL last used cell
Excel How To
There is already a FAQ on this subject but these solutions offer less code and a function and formula solution
As has been stated many times in this and other forums, excel gets "confused" as to how many cells actually have data in. This happens especially when data is entered and then cleared as opposed to deleting the cell. In this instance, the UsedRange property of the worksheet becomes incorrect. Here are 3 ways to get round it
1:
Via a macro
2:
Via a function
Note - for any used range that may not start in row 1, the following amendments should be used (thanks to GlennUK)
r = ActiveSheet.UsedRange.Rows.Count + ActiveSheet.UsedRange.Row - 1
Alternatively, for the columns:
c = ActiveSheet.UsedRange.columns.Count + ActiveSheet.UsedRange.column - 1
3:
Via a formula on a worksheet (thx to GlennUK for this one)
Do menu command Insert/Name/Define, and type a name of LastRow, and in "Refers To" box type :
=GET.DOCUMENT(10)
and press OK.
Now type =LastRow in any spare cell on the sheet, and the result will be the row number of the last used row.
This can also be used in the SUB (but not the function) by using the line:
LastRow = ExecuteExcel4Macro("GET.DOCUMENT(10)")
As a further point:
To find the ADDRESS of the end of the column with the most rows, you will need slightly more code:
Hope this is of help to you
Rgds
Geoff
![[hourglass]](/data/assets/smilies/hourglass.gif "[hourglass] [hourglass]")
As has been stated many times in this and other forums, excel gets "confused" as to how many cells actually have data in. This happens especially when data is entered and then cleared as opposed to deleting the cell. In this instance, the UsedRange property of the worksheet becomes incorrect. Here are 3 ways to get round it
1:
Via a macro
Code:
Sub FindLastRow()
r = ActiveSheet.UsedRange.Rows.Count
c = ActiveSheet.UsedRange.Columns.Count
LastRow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
End SubVia a function
Code:
Function FindLastRow()
r = ActiveSheet.UsedRange.Rows.Count
c = ActiveSheet.UsedRange.Columns.Count
FindLastRow = r
End FunctionNote - for any used range that may not start in row 1, the following amendments should be used (thanks to GlennUK)
r = ActiveSheet.UsedRange.Rows.Count + ActiveSheet.UsedRange.Row - 1
Alternatively, for the columns:
c = ActiveSheet.UsedRange.columns.Count + ActiveSheet.UsedRange.column - 1
3:
Via a formula on a worksheet (thx to GlennUK for this one)
Do menu command Insert/Name/Define, and type a name of LastRow, and in "Refers To" box type :
=GET.DOCUMENT(10)
and press OK.
Now type =LastRow in any spare cell on the sheet, and the result will be the row number of the last used row.
This can also be used in the SUB (but not the function) by using the line:
LastRow = ExecuteExcel4Macro("GET.DOCUMENT(10)")
As a further point:
To find the ADDRESS of the end of the column with the most rows, you will need slightly more code:
Code:
Sub FindLastCell()
Dim lRow As Long, lCol As Integer, mRow As Long, mCol As Integer
lCol = ActiveSheet.UsedRange.Columns.Count
mRow = 0
For i = 1 To lCol
lRow = Range(Cells(rows.count, i), Cells(rows.count, i)).End(xlUp).Row
If lRow > mRow Then
mRow = lRow
mCol = i
Else
End If
Next i
LastCell = Range(Cells(mRow, mCol), Cells(mRow, mCol)).Address
End SubHope this is of help to you
Rgds
Geoff