Counting cells which are != empty

[excel_user_1966]

I have:

------------------
Sub CountHowManyRowOfData()
MsgBox ActiveSheet.UsedRange.rows.Count
End Sub
---------------------

to count how many rows have data.

However, I've got formatted cells (with borders, etc) which are being counted in this too.... whilst i only wish to count how many rows from say the range B6 to I2000 are being used WITH DATA IN THEM. I don't want the formatted but technically empty cells in this count. Is there a way to do this?

 

[paelinck]

You could use the CurrentRegion property if the rows you want to count are adjacent:

Activesheet.cells(5,5).CurrentRegion.Rows.Count

should do the trick, supposing that the top left cell of your table is cell E5

Marc

 

[excel_user_1966]

I didn't realise that if a user using my spreadsheet were to use the Autofilter it would still count all the rows; is there anyway to only let it count the stuff that's SHOWING and the rows with data?

 

[Ivan F Moala]

Try;

<pre/>
Sub FilteredRow_Count()
Dim Area As Range
Dim Rowcount As Double

Rowcount = -1
'// [A1] is the start of your Filterer row
For Each Area In [A1].CurrentRegion.SpecialCells(xlVisible).Areas
Rowcount = Rowcount + Area.Rows.Count
Next

MsgBox Rowcount

End Sub

</pre>