Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

VBA Visual Basic for Applications (Microsoft) FAQ

Excel How To

How do I find the REAL last used cell by xlbo
Posted: 2 Jul 02 (Edited 8 Dec 06)

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

Via a macro


Sub FindLastRow()
r = ActiveSheet.UsedRange.Rows.Count
c = ActiveSheet.UsedRange.Columns.Count
LastRow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
End Sub
Via a function


Function FindLastRow()
r = ActiveSheet.UsedRange.Rows.Count
c = ActiveSheet.UsedRange.Columns.Count
FindLastRow = r
End 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

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 :


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:


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
    End If
Next i
LastCell = Range(Cells(mRow, mCol), Cells(mRow, mCol)).Address
End Sub

Hope this is of help to you

Back to VBA Visual Basic for Applications (Microsoft) FAQ Index
Back to VBA Visual Basic for Applications (Microsoft) Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close