Determining the last row in a spreadsheet
 

Sub Tester2()
Set rng = Cells.SpecialCells(xlLastCell)
MsgBox rng.Row

End Sub


--
Regards,
Tom Ogilvy

"Al Lind" wrote in message
...
I am writting a VB application that reads in an Excel
Spreadsheet. I need to determine the last row for
processing. I can't find the property anywhere. Is there
a method for doing this?

Determining the last row in a spreadsheet
 

Hi Al

Remember this is not always the last row with data
The last cell will be reset when you save the workbook.

Try to delete some data and run the sub again.
You see that it rember the last cell when you open the workbook

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Al Lind" wrote in message ...
Works great! Thanks Tom.



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Determining the last row in a spreadsheet
 

There can be problems with the method I provided in terms of interpretation.
This shows the lower right corner of the area, Excel considers to be in use.
That doesn't always represent the last row that has data - it may overstate
the row, but never understate it.

There are other factors which would cause excel to overstate the usedrange
besides having data in a cell, such as cell formatting and so forth.

Ron's method will show you that last cell that actually contains data.


--
Regards,
Tom Ogilvy


"Al Lind" wrote in message
...
Works great! Thanks Tom.



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Determining the last row in a spreadsheet
 

Generally not if you delete entire rows. I don't discount the method you
propose is more robust.

--
Regards,
Tom Ogilvy




"Ron de Bruin" wrote in message
...
Hi Al

Remember this is not always the last row with data
The last cell will be reset when you save the workbook.

Try to delete some data and run the sub again.
You see that it rember the last cell when you open the workbook

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Al Lind" wrote in message
...
Works great! Thanks Tom.



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Determining the last row in a spreadsheet
 

or use
Activesheet.UsedRange

--
Regards,
Tom Ogilvy

"Ron de Bruin" wrote in message
...
Hi Tom

Generally not if you delete entire rows
If I delete entire rows with your example it will not reset the last cell
Only when I save the file



--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Tom Ogilvy" wrote in message
...
Generally not if you delete entire rows. I don't discount the method
you
propose is more robust.

--
Regards,
Tom Ogilvy




"Ron de Bruin" wrote in message
...
Hi Al

Remember this is not always the last row with data
The last cell will be reset when you save the workbook.

Try to delete some data and run the sub again.
You see that it rember the last cell when you open the workbook

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Al Lind" wrote in message
...
Works great! Thanks Tom.



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Determining the last row in a spreadsheet
 

As an example:
Sub Tester9()
lastRow = ActiveSheet.Cells.SpecialCells(xlLastCell).Row
Debug.Print lastRow
Rows(lastRow).Offset(-1, 0).Resize(2).EntireRow.Delete
ActiveSheet.UsedRange
lastRow = ActiveSheet.Cells.SpecialCells(xlLastCell).Row
Debug.Print lastRow

End Sub


--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
or use
Activesheet.UsedRange

--
Regards,
Tom Ogilvy

"Ron de Bruin" wrote in message
...
Hi Tom

Generally not if you delete entire rows
If I delete entire rows with your example it will not reset the last
cell
Only when I save the file



--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Tom Ogilvy" wrote in message
...
Generally not if you delete entire rows. I don't discount the method
you
propose is more robust.

--
Regards,
Tom Ogilvy




"Ron de Bruin" wrote in message
...
Hi Al

Remember this is not always the last row with data
The last cell will be reset when you save the workbook.

Try to delete some data and run the sub again.
You see that it rember the last cell when you open the workbook

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Al Lind" wrote in message
...
Works great! Thanks Tom.



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Determining the last row in a spreadsheet
 

Hi Tom

MsgBox ActiveSheet.UsedRange.Rows.Count

When you delete entire rows or right click on the selection and choose delete this is working.
selecting the cells and press the delete button is not working.


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Tom Ogilvy" wrote in message ...
or use
Activesheet.UsedRange

--
Regards,
Tom Ogilvy

"Ron de Bruin" wrote in message
...
Hi Tom

Generally not if you delete entire rows
If I delete entire rows with your example it will not reset the last cell
Only when I save the file



--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Tom Ogilvy" wrote in message
...
Generally not if you delete entire rows. I don't discount the method
you
propose is more robust.

--
Regards,
Tom Ogilvy




"Ron de Bruin" wrote in message
...
Hi Al

Remember this is not always the last row with data
The last cell will be reset when you save the workbook.

Try to delete some data and run the sub again.
You see that it rember the last cell when you open the workbook

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Al Lind" wrote in message
...
Works great! Thanks Tom.



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!