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;

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