Return Count Of Visible Rows After Auto Filter

    I have tried and tried to get the VBA code working that will tell me the number of visible rows in an autofiltered set of data, but the result I seem to be getting is always "1". Below is the most simple form of the code that I am using (it is based on previous posts and tutorials on this site). Can someone determine from this what I am doing wrong? (I have also attached a workbook with sample data and the code)


    Code
    With ActiveSheet
        Set rnData = .UsedRange
        With rnData
            .AutoFilter Field:=1, Criteria1:="5"
            .Select                                                'demonstrate that the rnData range is valid
            lcount = .SpecialCells(xlCellTypeVisible).Rows.Count
        End With
    End With


    Thanks in advance for any assistance!


    Paul

    Re: Autofilter - Count Of Rows Is Always 1


    I wasn't sure either but I checked one of the Possible Answers at the top of the page - it's a question of getting things in the right order. Try this:

    Code
    Sub demo()
    
With ActiveSheet
    .UsedRange.AutoFilter Field:=1, Criteria1:="5"
    lcount = .AutoFilter.Range.Rows.SpecialCells(xlCellTypeVisible).Count
    MsgBox lcount
End With
    
End Sub

    Re: Autofilter - Count Of Rows Is Always 1


    You could use a formula method to find visible rows:


    =SUBTOTAL(2,A2:A3000)


    Better to create a dynamic named range for the second part of this formula though if your dataset changes.

    I am new to VBA - comments on how to improve my code are always welcome.

    Re: Autofilter - Count Of Rows Is Always 1


    Thanks you for the quick replies!


    StephenR, I used your suggestion, and it worked great. I am still trying to understand both why mine didn't work and yours did, but in the meantime, it gets me moving along!


    norie and Upside, thanks, too, for the replies, I will be looking into them to determine if they would better suit my needs, but for now on with my coding!

    Re: Autofilter - Count Of Rows Is Always 1


    The range is split over multiple areas.


    [h4]Cheers
    Andy    [/h4]

    Edited once, last by Carim: Added Code Tags to Andy's brilliant and very useful macro ... !!! ().

    Re: Autofilter - Count Of Rows Is Always 1


    You are a genious !!! Spent a couple of hours using count if, criteria, parameters after applying autofilter. Thanks for resolving this so simply!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login