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
--
www.brightfuture.ca/bright
My email address can be found on my site.

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

--
HTH...

Jim Thomlinson


"Johnny Bright" wrote:

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
--
www.brightfuture.ca/bright
My email address can be found on my site.

That worked perfectly! Thanks so much!

John
--
www.brightfuture.ca/bright
My email address can be found on my site.


"Jim Thomlinson" wrote:

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

--
HTH...

Jim Thomlinson


"Johnny Bright" wrote:

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
--
www.brightfuture.ca/bright
My email address can be found on my site.