Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Determining if Value In List | Excel Discussion (Misc queries) | |||
need help with determining a formula | Excel Worksheet Functions | |||
Determining a Date | Excel Discussion (Misc queries) | |||
Determining the age of an individual | Excel Discussion (Misc queries) | |||
Determining cellcontents | Excel Programming |