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)

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? ...

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)