If you know that only column A was used, you could do something like:

dim LastCell as range
with activesheet
set lastcell = .cells(.rows.count,"A").end(xlup)
end with



CyndyG wrote:

How could I use this code when the last cell of the data contains character
127(the end of file character) and all the data is in column A,there is only
1 worksheet.

"indiana1138" wrote:

Hello Simon,

Other folks have found that Excel does not work as advertised in this
respect. Try this code for something that might work:

Function LastCell(Optional ws As Worksheet) As Range
If ws Is Nothing Then Set ws = ActiveSheet
Set Rng = ws.Cells
Set LastCell = Rng(1)
On Error Resume Next
Set LastCell = Intersect( _
Rng.Find("*", Rng(1), xlValues, xlWhole, xlByRows, _
xlPrevious).EntireRow, _
Rng.Find("*", Rng(1), xlValues, xlWhole, xlByColumns, _
xlPrevious).EntireColumn)
End Function

Hope this helps,

Bob



--

Dave Peterson