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

--
Regards Ron de Bruin
http://www.rondebruin.nl


"johli" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>
>
> --
> johli
> ------------------------------------------------------------------------
> johli's Profile: http://www.excelforum.com/member.php...o&userid=27388
> View this thread: http://www.excelforum.com/showthread...hreadid=470917
>

[cush]

Try using the SUBTOTAL function.

"johli" wrote:

>
> 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
>
>
> --
> johli
> ------------------------------------------------------------------------
> johli's Profile: http://www.excelforum.com/member.php...o&userid=27388
> View this thread: http://www.excelforum.com/showthread...hreadid=470917
>
>

[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 wrote:
> 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
>
>

[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 wrote:
> 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
>
>

[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