+ Reply to Thread
Results 1 to 7 of 7

Autofilter/xlVisible problem when counting rows

  1. #1
    Registered User
    Join Date
    09-20-2005
    Posts
    8

    Autofilter/xlVisible problem when counting rows

    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

  2. #2
    Ron de Bruin
    Guest

    Re: Autofilter/xlVisible problem when counting rows

    Hi johli

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

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


    "johli" <johli.1vzl2c_1127772323.7175@excelforum-nospam.com> wrote in message
    news:johli.1vzl2c_1127772323.7175@excelforum-nospam.com...
    >
    > 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
    >




  3. #3
    cush
    Guest

    RE: Autofilter/xlVisible problem when counting rows

    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
    >
    >


  4. #4
    Rowan
    Guest

    Re: Autofilter/xlVisible problem when counting rows

    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
    >
    >


  5. #5
    Registered User
    Join Date
    09-20-2005
    Posts
    8
    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

  6. #6
    Rowan
    Guest

    Re: Autofilter/xlVisible problem when counting rows

    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
    >
    >


  7. #7
    Registered User
    Join Date
    09-20-2005
    Posts
    8
    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1