Smart questions
Smart answers
Smart people
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • 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!

Join Tek-Tips
*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.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

VBA Visual Basic for Applications (Microsoft) FAQ

Excel How To

How do I find the REAL last used cell
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

1:
Via a macro

CODE

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

CODE

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

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 Sub

Hope this is of help to you
Rgds
Geoff
hourglass

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