Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last cell
Hi Group,
This is a second post for this question and I am having a hard time explaining it, but here it goes again. Ctrl+End takes you to the last used cell in a worksheet. I am using version 2003. In previous versions, I could change the last used cell by deleting rows, but only by doing this with code. I don't seem to be able to do that anymore with this version. Has something changed or maybe I am missing a parameter? Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last cell
since xl97(SR-1?).. the last cell is reset on save. the last cell (as microsoft sees it) is the last cell which contains data, comments or FORMATS(fill/numberformat or else). you can force a recompute of lastcell: in code: by calling the usedrange method. but you may have to clear formats outside "your" usedrange first. Dim lng& lng = wks.UsedRange.Count Manually by DELETING all Rows and Columns beyond what you want the usedrange to be. HOWEVER: if row 65536 or column 256 or any cell therein contains any formatting.. you FIRST have to reset the formats else the newly inserted rows will get that format and your usedrange wont change. keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool "David" wrote: Hi Group, This is a second post for this question and I am having a hard time explaining it, but here it goes again. Ctrl+End takes you to the last used cell in a worksheet. I am using version 2003. In previous versions, I could change the last used cell by deleting rows, but only by doing this with code. I don't seem to be able to do that anymore with this version. Has something changed or maybe I am missing a parameter? Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last cell
David,
What is it, exactly, that you "don't seem able to do"? Delete rows with code? -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "David" wrote in message ... Hi Group, This is a second post for this question and I am having a hard time explaining it, but here it goes again. Ctrl+End takes you to the last used cell in a worksheet. I am using version 2003. In previous versions, I could change the last used cell by deleting rows, but only by doing this with code. I don't seem to be able to do that anymore with this version. Has something changed or maybe I am missing a parameter? Thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last cell
One more try. I have a worksheet, where I am putting a
table that is 1000 rows "tall" and it is below some existing data, which is some 300 rows "tall." Once this is done, the table is then deleted. If you do a Ctrl + End, you end up somewhere around row 1300, which is way below the data. If I recreate the table, the code finds the last used cell ie row 1300 and puts the table there. In previous versions I could change that last used cell by deleting rows with code, but I am not able to do that anymore. I can NOT change the last used cell. Does anyone know how I can change the the last used cell? The last used cell does not have to contain data or comments. As I mentioned, I had been able to delete rows in previous versions and permanently change the last used cell ie if I deleted rows 1000 to 1300, where 1300 had been the last used row, a new last used cell was created and became row 1000. It also did not matter that some of these cells contained data. Thanks for your help. -----Original Message----- Hi Group, This is a second post for this question and I am having a hard time explaining it, but here it goes again. Ctrl+End takes you to the last used cell in a worksheet. I am using version 2003. In previous versions, I could change the last used cell by deleting rows, but only by doing this with code. I don't seem to be able to do that anymore with this version. Has something changed or maybe I am missing a parameter? Thanks . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last cell
Hi Cool,
I am not quite sure if I explained myself well enough. I am trying to change the last used cell from cell 1300 to cell 300. If I press Crtl + End, where the last used cell is 1300, is it possible to change what happens when I press Ctrl + End, so that it does not end up at row 1300? Thanks for your help. -----Original Message----- since xl97(SR-1?).. the last cell is reset on save. the last cell (as microsoft sees it) is the last cell which contains data, comments or FORMATS(fill/numberformat or else). you can force a recompute of lastcell: in code: by calling the usedrange method. but you may have to clear formats outside "your" usedrange first. Dim lng& lng = wks.UsedRange.Count Manually by DELETING all Rows and Columns beyond what you want the usedrange to be. HOWEVER: if row 65536 or column 256 or any cell therein contains any formatting.. you FIRST have to reset the formats else the newly inserted rows will get that format and your usedrange wont change. keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool "David" wrote: Hi Group, This is a second post for this question and I am having a hard time explaining it, but here it goes again. Ctrl+End takes you to the last used cell in a worksheet. I am using version 2003. In previous versions, I could change the last used cell by deleting rows, but only by doing this with code. I don't seem to be able to do that anymore with this version. Has something changed or maybe I am missing a parameter? Thanks . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last cell
Hi David,
To positively make a cell the last cell in the worksheet http://www.mvps.org/dmcritchie/excel...m#makelastcell To run through the entire workbook to fix last cell problems, this will usually work. It does not delete anything, but it does not test to see if it worked -- it is real fast. http://www.mvps.org/dmcritchie/excel...l.htm#resetall Neither of the above will save the workbook, so change will not be seen if you exit without saving. Difference between what you do manually and what you can do in a macro. If you manually delete rows and columns to effect change to the lastcell indication, then you have to save the file. I have Excel 2000. What version do you have. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "David" wrote in message ... Hi Cool, I am not quite sure if I explained myself well enough. I am trying to change the last used cell from cell 1300 to cell 300. If I press Crtl + End, where the last used cell is 1300, is it possible to change what happens when I press Ctrl + End, so that it does not end up at row 1300? Thanks for your help. -----Original Message----- since xl97(SR-1?).. the last cell is reset on save. the last cell (as microsoft sees it) is the last cell which contains data, comments or FORMATS(fill/numberformat or else). you can force a recompute of lastcell: in code: by calling the usedrange method. but you may have to clear formats outside "your" usedrange first. Dim lng& lng = wks.UsedRange.Count Manually by DELETING all Rows and Columns beyond what you want the usedrange to be. HOWEVER: if row 65536 or column 256 or any cell therein contains any formatting.. you FIRST have to reset the formats else the newly inserted rows will get that format and your usedrange wont change. keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool "David" wrote: Hi Group, This is a second post for this question and I am having a hard time explaining it, but here it goes again. Ctrl+End takes you to the last used cell in a worksheet. I am using version 2003. In previous versions, I could change the last used cell by deleting rows, but only by doing this with code. I don't seem to be able to do that anymore with this version. Has something changed or maybe I am missing a parameter? Thanks . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last cell
Should have noted that you might want to check
File, Properties, size before and after. Though if you close without saving then no change will have actually taken place |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Code to copy the formulae of one cell to all the cell in the rangewith the specific cell and columnnumber changing Options | Excel Discussion (Misc queries) | |||
Code to copy the formulae of one cell to all the cell in the rangewith the specific cell and columnnumber changing | Excel Discussion (Misc queries) | |||
Populate a cell if values in cell 1 and cell 2 match cell 3 and 4 | Excel Worksheet Functions | |||
How to create/run "cell A equals Cell B put Cell C info in Cell D | Excel Discussion (Misc queries) | |||
Question: Cell formula or macro to write result of one cell to another cell | Excel Programming |