Learn how to a build a cloud-first strategyRegister Now

x
  • 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.
0
Chuiksgirl
Asked:
Chuiksgirl
4 Solutions
 
SiddharthRoutCommented:
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
 
leonstrykerCommented:
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
 
royhsiaoCommented:
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
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.

 
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 BottomleyCommented:
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

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