Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Go to the Last Row for Counting Purposes
How do you go to the last row that contains data in a spreadsheet if there are numerous rows that do not contain any data? What I really want to do is to be able to determine the number of "blocks" of data that I have. The "blocks" are separated by empty rows. The problem is that the number of "blocks" will increase over time as more data is added. I'm thinking that once I determine the last row number of the last block, I could use that as a counter (and divide by 14 since the spacing between blocks is 14) Here is an example of the data that I'm working with: xxxxxxxxxx yyyyyyyyyy "block 1" zzzzzzzzzz 14 rows aaaaaaaaa bbbbbbbbb "block 2" cccccccccccc 14 rows mmmmmmm nnnnnnnnnn "block 3" oooooooooo 14 rows more data to be added in the future Can you help? Thanks. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Go to the Last Row for Counting Purposes
Assuming column A can be used to make the determination:
Set rng = Columns(1).specialCells(xlConstants) set lastBlock= rng.Areas(rng.Areas.count) lastrow = LastBlock.Rows(LastBlock.rows.count).Row msgbox rng.Areas.count & "Blocks, last row: " & LastRow or LastRow = cells(rows.Count,"A").End(xlup).Row msgBox "Last Row is " & LastRow -- Regards, Tom Ogilvy Novice wrote in message ... How do you go to the last row that contains data in a spreadsheet if there are numerous rows that do not contain any data? What I really want to do is to be able to determine the number of "blocks" of data that I have. The "blocks" are separated by empty rows. The problem is that the number of "blocks" will increase over time as more data is added. I'm thinking that once I determine the last row number of the last block, I could use that as a counter (and divide by 14 since the spacing between blocks is 14) Here is an example of the data that I'm working with: xxxxxxxxxx yyyyyyyyyy "block 1" zzzzzzzzzz 14 rows aaaaaaaaa bbbbbbbbb "block 2" cccccccccccc 14 rows mmmmmmm nnnnnnnnnn "block 3" oooooooooo 14 rows more data to be added in the future Can you help? Thanks. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Names of ratios and their purposes | Excel Discussion (Misc queries) | |||
Splitting hours for payroll purposes | Excel Discussion (Misc queries) | |||
Need list of Formulas and their Purposes | Excel Discussion (Misc queries) | |||
Is there a way for excel to recognise that €0.00 = 0 for the purposes IF function ? | Excel Discussion (Misc queries) | |||
create macro for learning purposes | Excel Worksheet Functions |