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?

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

"Simon Shaw" wrote:

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?

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

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" wrote in message
...
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

"Simon Shaw" wrote:

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?

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

"Bob Phillips" wrote:

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" wrote in message
...
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

"Simon Shaw" wrote:

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?

Me neither, usually only in responses here <bg

Bob


"Jim Thomlinson" wrote in message
...
Thanks Bob I will remenber that. Can you tell I don't use used range a
lot. :)

"Bob Phillips" wrote:

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" wrote in
message
...
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

"Simon Shaw" wrote:

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?

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.

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

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