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.


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

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

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 advance

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.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Thank you again Claus!.

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

The range will have blanks and NO apart from YES.