count cells after

[Peter]

Hello all,
i need to count the cells after doing the
Data>Filter>Autofilter of a filed but excel counts all the
cells like they are not filtered.
Thanks in advance.
Pit

 

[Frank Kabel]

Hi
have a look at
http://www.contextures.com/xlautofilter02.html#Count

 

[Bernie Deitrick]

Pit,

Range("FilteredRange").SpecialCells(xlCellTypeVisible).Count

If you want the rows only (the number of records returned by the filter),
then you need to limit the FilteredRange to a single column:

Range("FilteredRange").Columns(1).SpecialCells(xlCellTypeVisible).Cells.Coun
t

HTH,
Bernie
MS Excel MVP

 

[Peter]

Thanks all,
but can i put i in a module, have you any exmaples to
easier this work?
Thanks again
PIT

 

[Bernie Deitrick]

Pit,

Suppose you have a data table in cells A1:F100, and you want only those
cells with value greater than 4 in column A:

Sub TellMe()
Msgbox Range("A1:F100").SpecialCells(xlCellTypeVisible).Count
Range("A1:F100").AutoFilter Field:=1, Criteria1:=">4", Operator:=xlAnd
Msgbox Range("A1:F100").SpecialCells(xlCellTypeVisible).Count
End Sub

HTH,
Bernie
MS Excel MVP

 

[PLS]

I need only to count the cells filtered (after autofilter) a counter that
counts ...and so if i cnacel the conditions to the code below ..is true??
Thanks
Pit

 

[Bernie Deitrick]

Pit,

I was just showing you how the code can be used. If you have already
filtered your range, then the count of the remaining visible cells is

Range("Address of whatever range you are
filtering").SpecialCells(xlCellTypeVisible).Count

I don't know how to explain it better than that.

HTH,
Bernie
MS Excel MVP

 

[PIT]

THANKS AGAIN

-----Original Message-----
Pit,

I was just showing you how the code can be used. If you have already
filtered your range, then the count of the remaining visible cells is

Range("Address of whatever range you are
filtering").SpecialCells(xlCellTypeVisible).Count

I don't know how to explain it better than that.

HTH,
Bernie
MS Excel MVP




.