Selecting just my text

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to find the bottom of my text. I have added some text and deleted it
and when I do this:

Range("A1").Select
ActiveCell.SpecialCells(xlLastCell).Select
ActiveCell.Offset(1, -10).Range("A1").Select

it goes to the bottom of wherever I have had data even if it has been
deleted as in, if I delete rows 10 to 20, the code above will still take me
to row 20 when I want to go to row 10 where my text is. How can I get around
this?

All help greatly appreciated.

J
 
Here is a function that returns the true last cell on the sheet. Execute Sub
Test() to give it a try...

Sub test()
LastCell().Select
End Sub

Public Function LastCell(Optional ByVal wks As Worksheet) As Range
Dim lngLastRow As Long
Dim intLastColumn As Integer

If wks Is Nothing Then Set wks = ActiveSheet
On Error Resume Next
lngLastRow = wks.Cells.Find(What:="*", _
After:=wks.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
intLastColumn = wks.Cells.Find(What:="*", _
After:=wks.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
If lngLastRow = 0 Then
lngLastRow = 1
intLastColumn = 1
End If
Set LastCell = wks.Cells(lngLastRow, intLastColumn)

End Function
 
Back
Top