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

F

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

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 __
 
R

Rick Rothstein

Give this statement a try...

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top