.Cells.SpecialCells(xlLastCell)
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? |
.Cells.SpecialCells(xlLastCell)
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? |
.Cells.SpecialCells(xlLastCell)
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 |
.Cells.SpecialCells(xlLastCell)
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? |
.Cells.SpecialCells(xlLastCell)
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? |
.Cells.SpecialCells(xlLastCell)
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? |
.Cells.SpecialCells(xlLastCell)
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. |
.Cells.SpecialCells(xlLastCell)
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 |
.Cells.SpecialCells(xlLastCell)
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 |
All times are GMT +1. The time now is 01:16 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com