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.

LINK TO THIS FORUM!

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

Tek-Tips LinkedIn® Group

Partner With Us!

"Best Of Breed" Forums Add Stickiness To Your Site
Partner Button
(Download This Button Today!)

Feedback

"...Keep up the good work - excellent site - i'd been looking for something like this for ages !..."
More...

Geography

Where in the world do Tek-Tips members come from?
Click Here To Find Out!

Tek-Tips Shirts!

Get Tek-Tips Swag
Get your
Tek-Tips Forums
SWAG here!
Home > Forums > Programmers > Languages > VBA Visual Basic for Applications (Microsoft) > FAQs

VBA Visual Basic for Applications (Microsoft) FAQ

Excel How To

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