Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to count # of "non-empty" rows of data
Hello there,
I would like to find the number of rows with data in it while going from top to bottom of a worksheet. This code does the trick: lngLastUsedRowInColX = ActiveSheet.Cells(Rows.Count, ColX).End(xlUp).Row However, I would like the count to be sensitive to a "blank" row (i.e., without any data in it). So, give me the last row count before a blank row. Here is an example: Row 1: 12 Row 2: 18 Row 3: 9 Row 4: Row 5: Row 6: 5 Row 7: 44 My code above returns "7 rows with data". However, I would like to have the result "3 rows of code before the first "blank" row". Any idea how I can achieve this, presumably by extending the code above? Thank you. By the way, this question is directly related to a previous question (http://www.microsoft.com/office/comm...4-bd178158ed6b) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to count # of "non-empty" rows of data
hi, !
this code line returns the row-number for the first empty cell in a column: lngLastUsedRowInColX = Columns(ColX).Cells.Find(Empty).Row BUT... *IF* ColX is completely empty returns (row) 2 to correct this (possible) situation... lngLastUsedRowInColX = Columns(ColX).Cells.Find(What:=Empty, After:=Cells(Rows.Count, ColX)).Row hth, hector. __ OP __ FSPH wrote in message ... Hello there, I would like to find the number of rows with data in it while going from top to bottom of a worksheet. This code does the trick: lngLastUsedRowInColX = ActiveSheet.Cells(Rows.Count, ColX).End(xlUp).Row However, I would like the count to be sensitive to a "blank" row (i.e., without any data in it). So, give me the last row count before a blank row. Here is an example: Row 1: 12 Row 2: 18 Row 3: 9 Row 4: Row 5: Row 6: 5 Row 7: 44 My code above returns "7 rows with data". However, I would like to have the result "3 rows of code before the first "blank" row". Any idea how I can achieve this, presumably by extending the code above? ... |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to count # of "non-empty" rows of data
Give this statement a try...
lngLastUsedRowInColX = Columns(ColX).SpecialCells(xlCellTypeBlanks)(1).Ro w -- Rick (MVP - Excel) "FSPH" wrote in message ... Hello there, I would like to find the number of rows with data in it while going from top to bottom of a worksheet. This code does the trick: lngLastUsedRowInColX = ActiveSheet.Cells(Rows.Count, ColX).End(xlUp).Row However, I would like the count to be sensitive to a "blank" row (i.e., without any data in it). So, give me the last row count before a blank row. Here is an example: Row 1: 12 Row 2: 18 Row 3: 9 Row 4: Row 5: Row 6: 5 Row 7: 44 My code above returns "7 rows with data". However, I would like to have the result "3 rows of code before the first "blank" row". Any idea how I can achieve this, presumably by extending the code above? Thank you. By the way, this question is directly related to a previous question (http://www.microsoft.com/office/comm...4-bd178158ed6b) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Create Pivot Table Data with Column "Sum" rather than "count" defa | Excel Discussion (Misc queries) | |||
Pivot (Data Items "Count" vs "Sum") | Excel Discussion (Misc queries) | |||
Empty a cell if the values equal to "IN" , "MC" or "PP" | Excel Programming | |||
delete rows if cell in row contains "a" or "o" or empty | Excel Programming | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel |