Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last cell
Hi Group,
I am using - ActiveCell.SpecialCells(xlLastCell).Select - to find the lst cell. Then I am using - Rows((TopRow) & ":" & (BottomRow)).Delete - to delete a range of rows. What I expected was that the "xlLastCell" would change, but it did not. I am essentailly trying to "raise" the location of the "LastCell" to a row higher than it currectly occupies. In fact I thought this was the only way to do it? I am using VB 6.3 in Office Professional in Excel 2003. Has something changed? -- David |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last cell
Hi David,
This is standard Excel behaviour. To reset the LastCell you can either save the workbook or execute the following: ActiveSheet.UsedRange Naturally, if controlling Excel from VB you would need to qualify the above with a reference to the Excel Application. Something like: xlApp.ActiveSheet.UsedRange -- John Green Sydney Australia "David" wrote in message ... Hi Group, I am using - ActiveCell.SpecialCells(xlLastCell).Select - to find the lst cell. Then I am using - Rows((TopRow) & ":" & (BottomRow)).Delete - to delete a range of rows. What I expected was that the "xlLastCell" would change, but it did not. I am essentailly trying to "raise" the location of the "LastCell" to a row higher than it currectly occupies. In fact I thought this was the only way to do it? I am using VB 6.3 in Office Professional in Excel 2003. Has something changed? -- David |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last cell
I am not trying to find the used range. I am trying to change the Last used
row from 100 to 80. In previous versions, I believe that I deleted rows 100 to 81 and that raised the last row used to 80. I did not have to save the workbook and reopen it to do this either. Thanks "John Green" wrote: Hi David, This is standard Excel behaviour. To reset the LastCell you can either save the workbook or execute the following: ActiveSheet.UsedRange Naturally, if controlling Excel from VB you would need to qualify the above with a reference to the Excel Application. Something like: xlApp.ActiveSheet.UsedRange -- John Green Sydney Australia "David" wrote in message ... Hi Group, I am using - ActiveCell.SpecialCells(xlLastCell).Select - to find the lst cell. Then I am using - Rows((TopRow) & ":" & (BottomRow)).Delete - to delete a range of rows. What I expected was that the "xlLastCell" would change, but it did not. I am essentailly trying to "raise" the location of the "LastCell" to a row higher than it currectly occupies. In fact I thought this was the only way to do it? I am using VB 6.3 in Office Professional in Excel 2003. Has something changed? -- David |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last cell
Every version of Excel, since it first appeared on the Macintosh in the mid
80s, has exhibited the same behaviour. Deleting data or rows does not reset the last used row (or the last used column or the used range). Prior to Excel 97 (if my memory serves me), it was necessary to Save the worksheet or workbook to reset the last used row/column. It is not necessary to close and reopen the file. Just Save it. In the latest versions of Excel the same effect can be achieved with wks.UsedRange where wks is a reference to the worksheet object. -- John Green Sydney Australia "David" wrote in message ... I am not trying to find the used range. I am trying to change the Last used row from 100 to 80. In previous versions, I believe that I deleted rows 100 to 81 and that raised the last row used to 80. I did not have to save the workbook and reopen it to do this either. Thanks "John Green" wrote: Hi David, This is standard Excel behaviour. To reset the LastCell you can either save the workbook or execute the following: ActiveSheet.UsedRange Naturally, if controlling Excel from VB you would need to qualify the above with a reference to the Excel Application. Something like: xlApp.ActiveSheet.UsedRange -- John Green Sydney Australia "David" wrote in message ... Hi Group, I am using - ActiveCell.SpecialCells(xlLastCell).Select - to find the lst cell. Then I am using - Rows((TopRow) & ":" & (BottomRow)).Delete - to delete a range of rows. What I expected was that the "xlLastCell" would change, but it did not. I am essentailly trying to "raise" the location of the "LastCell" to a row higher than it currectly occupies. In fact I thought this was the only way to do it? I am using VB 6.3 in Office Professional in Excel 2003. Has something changed? -- David |
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 |