Excel2000: finding last row of used range
 

Hi

In my procedure I need to determine last used row on worksheet, and I tried
with:
LastRow = Sheets("SheetName").Cells.SpecialCells(xlCellTypeL astCell).Row

At start it looked fine - until I found, that when I p.e. delete some rows
from used range, the SpecialCells(xlCellTypeLastCell) isn't actualized until
I haven't saved my workbook.

The procedure sets the number of premade rows - formated and with formulas
ready for use - to number determined by user. It simply deletes abundant
rows (there is a limit for minimum number of rows left) from bottom of used
range, or adds a number of copies of last row(s).

To save the workbook every time before to reset it can be too
time-consuming. Is there a better way to determine the number of last used
row on worksheet?


Thanks in advance for your help
--
Arvi Laanemets
(When sending e-mail, use address arvil<Attarkon.ee)

Excel2000: finding last row of used range
 

Hi Arvi,

Arvi Laanemets schrieb:
In my procedure I need to determine last used row on worksheet, and I tried
with:
LastRow = Sheets("SheetName").Cells.SpecialCells(xlCellTypeL astCell).Row

At start it looked fine - until I found, that when I p.e. delete some rows
from used range, the SpecialCells(xlCellTypeLastCell) isn't actualized until
I haven't saved my workbook.

try this:

LastRow = Sheets("SheetName").Cells.Find("*", searchdirection:=xlPrevious).Row

--
Regards
Melanie Breden
- Microsoft MVP für Excel -

http://excel.codebooks.de (Das Excel-VBA Codebook)

Excel2000: finding last row of used range
 

Thanks! It did it!


--
Arvi Laanemets
(When sending e-mail, use address arvil<Attarkon.ee)



"Melanie Breden" wrote in message
...
Hi Arvi,

Arvi Laanemets schrieb:
In my procedure I need to determine last used row on worksheet, and I
tried
with:
LastRow = Sheets("SheetName").Cells.SpecialCells(xlCellTypeL astCell).Row

At start it looked fine - until I found, that when I p.e. delete some
rows
from used range, the SpecialCells(xlCellTypeLastCell) isn't actualized
until
I haven't saved my workbook.

try this:

LastRow = Sheets("SheetName").Cells.Find("*",
searchdirection:=xlPrevious).Row

--
Regards
Melanie Breden
- Microsoft MVP für Excel -

http://excel.codebooks.de (Das Excel-VBA Codebook)