Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 612
  • Last Modified:

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.
4 Solutions
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

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
    '/ nothing found on the sheet (it is completely blank)
    Set TrueLastCell = Range("$A$1")
    Set oLastColumn = Nothing
    Set oLastRow = Nothing
    Set oSheet = Nothing
End Function
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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

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


Application.Goto range("A" & lastCell + 1), true
Chris BottomleyCommented:

Will give you the last cell whereas


will give the next cell down in one.

have you tried CTRL [down arrow]?

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now