.Cells.SpecialCells(xlLastCell)

[Simon Shaw]

using the code ActiveSheet.Cells.SpecialCells(xlLastCell) normally works
fine. But, sometimes after working on a sheet for a while, the code returns
the address to cell IV65536 - the last cell in the sheet. I have tried Clear
=> All from the unused columns and rows, deleting the unused columns and
rows, but once the sheet thinks IV65536 is the last cell, it is stuck. how
can I clear up the sheet, so it correctly calculates the last cell?

 

[Jim Thomlinson]

Last cell is not updated until you save. Deleting the rows and columns will
fix the problem, but not until you save the spreadsheet. That is the problem
with using last cell. For that reason you are better off using either
..usedrange or .end

HTH

 

[indiana1138]

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

 

[Bob Phillips]

Usedrange also suffers that problem Jim.

To be safe, it is best to reset as described in
http://www.contextures.com/xlfaqApp.html#Unused

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Jim Thomlinson]

Thanks Bob I will remenber that. Can you tell I don't use used range a lot.

 

[Bob Phillips]

Me neither, usually only in responses here <bg>

Bob

 

[nathpai]

Alright.
We dont use last cell as well as usedrange.
Then how do we find out the actual last cell?
Kindly post the solution so that we can use the better function then
these sloppy ones.

Thanks.
Nathpai.

 

[CyndyG]

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.

 

[Dave Peterson]

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