How to count # of "non-empty" rows of data

[FSPH]

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 questio
(http://www.microsoft.com/office/com...&p=1&tid=172b7524-f20b-401c-a5f4-bd178158ed6b)

 

[Héctor Miguel]

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 __

 

[Rick Rothstein]

Give this statement a try...

lngLastUsedRowInColX = Columns(ColX).SpecialCells(xlCellTypeBlanks)(1).Row