Count After Filter in macro

[cvhar87]

I'm trying to count visible number of cells in a specific column afte
auto filter in macro. Here's the code I'm trying. I get 389 as the fina
answer, whereas the visible cells are only 7. othdet1 is a range an
othdet is an integer.


Set othdet1 = Sheets("OTHERS").Range("I4:I400")

othdet = Range("i4:i400").SpecialCells(xlCellTypeVisible).Count

Sheets("OTHERS").Range("N4") = othdet

The range I4:i400 is a result of an auto filter from parent sheet.

where am I going wrong?
Thanks in advanc

 

[Claus Busch]

Hi,

Am Tue, 2 Jul 2013 16:09:15 +0100 schrieb cvhar87:

I'm trying to count visible number of cells in a specific column after
auto filter in macro. Here's the code I'm trying. I get 389 as the final
answer, whereas the visible cells are only 7. othdet1 is a range and
othdet is an integer.

try:
Set othdet1 = Sheets("OTHERS").Range("I4:I400")

othdet = WorksheetFunction.Subtotal(3, othdet1)

Sheets("OTHERS").Range("N4") = othdet


Regards
Claus B.

 

[cvhar87]

Hi,

Am Tue, 2 Jul 2013 16:09:15 +0100 schrieb cvhar87:
-

try:
Set othdet1 = Sheets("OTHERS").Range("I4:I400")

othdet = WorksheetFunction.Subtotal(3, othdet1)

Sheets("OTHERS").Range("N4") = othdet


Regards
Claus B.

Thank you again Claus!.

Say if i Want to Count occurrences of only YES , in the filtered result
in column I4:I400? How do i go about it?

The range will have blanks and NO apart from YES