Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Winnipeg, Canada
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Counting Rows (Excel 2000 SR-1/Excel 97)

    Hi,

    How do I get a count of the rows containing data on a Filtered set of data?

    Sofar, I've only been able to get a count, average etc that includes all the data for a filtered range eg A2:A32999.

    Thanx for help <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

  2. #2
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Counting Rows (Excel 2000 SR-1/Excel 97)

    KenM

    This is tricky. You have to select the Visible Rows FIRST and then get the count.

    Dim lHowManyRowsFiltered As Long

    With Selection
    With .SpecialCells(xlCellTypeVisible)
    lHowManyRowsFiltered = .Rows.Count
    End With
    End With

    HTH

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Counting Rows (Excel 2000 SR-1/Excel 97)

    You can use the SUBTOTAL function to evaluate only the visible rows in a filtered list, e.g to get a count use =SUBTOTAL(3,A2:A32999). The first argument determines the function to be performed, 1=AVERAGE, 2=COUNT, 3 = COUNTA, 9 = SUM etc. For a full list look up SUBTOTAL in Help.

    Andrew C

Posting Permissions

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