Autofilter/xlVisible problem when counting rows

[johli]

Hi

Have some problems here that I hope someone smarter than me can
solve...

I'm trying to find the number of cells in an autofilter range matching
a specific criteria but I can't even get a correct count of all visible
cells.

Have tried the following code:

Function CountRows()
Dim rng As Range
Set rng = ActiveSheet.AutoFilter.Range

CountRows = rng.Columns(1).SpecialCells(xlVisible).Count - 1
End Function

This will always return all cells not just the visible ones...

If I run a Sub as a macro like:
Sub CountRows()
Dim rng As Range
Set rng = ActiveSheet.AutoFilter.Range

msgbox rng.Columns(1).SpecialCells(xlVisible).Count - 1
End Sub

It will return the correct count...

WHY??? Why doesn´t the function do the same when used in the
worksheet??

/Regards Johan

 

[Ron de Bruin]

Hi johli

See
http://www.contextures.com/xlautofilter02.html#Count

 

[Guest]

Try using the SUBTOTAL function.

 

[Rowan]

You can use a formula like this. This example counts all visible cells
in column A with the text Rowan.

=SUMPRODUCT(--(SUBTOTAL(3,OFFSET(INDEX(A2:A100,1,1),ROW(A2:A100)-ROW(INDEX(A2:A100,1,1)),0))=1),--(A2:A100="Rowan"))

Hopet this helps
Rowan

 

[johli]

It works using subtotal and sumproduct to count matching rows but since
I have a large dataarea, 25000 rows and 80 columns It takes a lot of
time to calculate.

Why won´t the function I posted work?? Is there a special case when
working with autofilter and specialcells to make the function call work
allright??

Can someone tell me why the same code returns the correct count when
used in a Sub and run as a Macro but not as a function....

/Johan

 

[Rowan]

Hi Johan

I don't know why your function is not working but if you have 25000 rows
and 80 columns you should be aware that there is a bug in the
specialcells method in that it can only hold 8192 non contiguous ranges.
If you data is filtered in such a way that:
rng.Columns(1).SpecialCells(xlVisible)
is asked to return more than 8192 non contiguous cells it will return
only one range equal to the whole of column(1)...and therefore your
count will be equal to the total number of rows visible or not.

I am not saying that this is what is causing your current problem but
this may raise its head if you do go further down this route.

Regards
Rowan

 

[johli]

Thanks

Its a total of 25000 rows, but the filter will not show more than 8000
rows at any time so I guess that not the problem

Been testing with a subset of rows too, like 200 rows and it´s still
not working

Might try using On_update and to use the macro and alter the
appropriate cell instead of a function, although it´s not as clean and
nice

/Johan