Excel macro to find and go to the last cell with text in it

I have an Excel 2007 spreadsheet which I share with others.  We are constantly adding text to it and it is currently up to row 305.  When I want to get to the last cell with text in it I press  CTRL + END, but it takes me to row 722 every time.  I tried deleting rows after row 305 and saving it, but CTRL + END still takes me to row 722.  Can I get a macro that will find the last row of text and then go to that row, so I can arrow down and start entering in the next row.  I plan to make the macro part of spreadsheet so that anyone who is using the spreadsheet can access the macro.  Thanks.
ChuiksgirlAsked:
Who is Participating?
 
leonstrykerConnect With a Mentor Commented:
Since the early days of Excel 5 I been using a variation of this function to find the last cell in a sheet:

Public Function TrueLastCell(Optional ByVal sSheet As String, _
                    Optional ByVal sBook As String) As Range
Dim oSheet As Worksheet
Dim oLastRow As Range
Dim oLastColumn As Range
    On Error GoTo ErrorHandler
    '/ set worksheet object
    sBook = IIf(sBook = "", ThisWorkbook.Name, sBook)
    sSheet = IIf(sSheet = "", ActiveSheet.Name, sSheet)
    Set oSheet = Workbooks(sBook).Worksheets(sSheet)
    '/ find the last row and last column
    Set oLastRow = oSheet.Cells.Find("*", oSheet.Cells(1048576, 16384), _
     xlFormulas, xlPart, xlByRows, xlPrevious, False)
    Set oLastColumn = oSheet.Cells.Find("*", oSheet.Cells(1048576, 16384), _
     xlFormulas, xlPart, xlByColumns, xlPrevious, False)
    '/ return the address
    Set TrueLastCell = oSheet.Cells(oLastRow.Row, oLastColumn.Column)
    GoTo Cleanup
    Exit Function
ErrorHandler:
    '/ nothing found on the sheet (it is completely blank)
    Set TrueLastCell = Range("$A$1")
Cleanup:
    Set oLastColumn = Nothing
    Set oLastRow = Nothing
    Set oSheet = Nothing
End Function
0
 
SiddharthRoutConnect With a Mentor Commented:
Please paste this in a module and then run it :)

Sub Sample()
    lastrow = ActiveSheet.Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
    ActiveSheet.Cells(lastrow, 1).Select
End Sub

Open in new window


Sid
0
 
royhsiaoConnect With a Mentor Commented:
try this
Sub finda()
Dim r As Range
Set r = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
If Not r Is Nothing Then MsgBox "Last row is " & r.Row
End Sub

Open in new window

0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
sdwalkerCommented:
When you find the last row, you should do this as your last step.

Application.Goto range("A" & lastCell), true

or

Application.Goto range("A" & lastCell + 1), true
0
 
Chris BottomleyConnect With a Mentor Software Quality Lead EngineerCommented:
cells(rows.count,activecell.Column).End(xlUp).select

Will give you the last cell whereas

cells(rows.count,activecell.Column).End(xlUp).offset(1,0).select

will give the next cell down in one.

Chris
0
 
rlarianCommented:
have you tried CTRL [down arrow]?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.