Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Autofilter (Excel 2000)

    I am doing an autofilter on a list and then deleting the filtered list. Are there a way of getting the value through VBA for the number of records showing in the status bar after doing a specific filter

    Thanks

    Mario

  2. #2
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Autofilter (Excel 2000)

    The only way that I can do it is count them:
    <pre>With Range("DataTable")
    .AutoFilter
    .AutoFilter field:=31, Criteria1:="3", Operator:=xlTop10Items
    MsgBox .SpecialCells(xlCellTypeVisible).Rows.Count ' Does not work
    iV = 0
    For i = 1 To .Rows.Count
    If Not .Rows(i).Hidden Then iV = iV + 1
    Next i
    MsgBox iV ' Works!
    End With</pre>

    Can't figure why SpecialCells doesn't work. It does work fine though if you want to copy the filtered list to another location. HTH --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  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: Autofilter (Excel 2000)

    When you use the SUBTOTAL function on a AutoFilter result , only the visible rows are enumertaed, so that you could use the following VBA code to count the number of rows visible in a range A1:A5000 that has an autofilter applied (assuming row1 contains a header:<pre>Dim NumRows As Long
    NumRows = Application.WorksheetFunction.Subtotal(3, Range("A2:A5000"))</pre>

    It should give the same result when used on a worksheet as <pre> =SUBTOTAL(3,"A2:A5000")</pre>

    You can change the first argument (3 for CountA) in both cases in order to Sum (9) or Average (1) etc. Check Sutotal in Excel Help for more values.

    Andrew C

  4. #4
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Autofilter (Excel 2000)

    Brilliant, Andrew! <img src=/S/clever.gif border=0 alt=clever width=15 height=15> That's something I'll need to remember, but it's tough for <img src=/S/gramps.gif border=0 alt=gramps width=20 height=20> to do that <img src=/S/exclamation.gif border=0 alt=exclamation width=15 height=15>
    There are two ways to add Subtotals to my example:
    <pre> MsgBox [Subtotal(3,DataTableProfits)]
    MsgBox Application.WorksheetFunction.Subtotal(3, .Columns(31))</pre>

    where DataTableProfits is an named range which is column 31 of DataTable. What is the formula in Excel for getting just column 31 of DataTable? I suspect it involves the Offset function which makes my head hurt! <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

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

    Re: Autofilter (Excel 2000)

    Sammy,

    There is no real need for using Offset as <pre> Application.WorksheetFunction.Subtotal(3, [DataTable].Columns(31))</pre>

    will subtotal column 31 of the range (which may or may not be column AE).

    The only things to take with is that if th erange includes the header row, you subtract 1 from the answer. Also you need to know that the column you are counting has no blanks if the object is to count the number records in the filter. If all cells of all columns have an entry, it does not matter which column you count on.

    Andrew

  6. #6
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Autofilter (Excel 2000)

    Andrew, actually, I was trying to fix the bracketed [] formula which needed the Excel (not VBA) OFFSET worksheet function. I forced myself to learn it and discovered that the slickest formula would be:
    <pre> MsgBox [SUBTOTAL(3,OFFSET(DataTable,0,30,,1))]</pre>

    But, both the offset Excel function and range method are UGLY! <img src=/S/sick.gif border=0 alt=sick width=15 height=15> I try to avoid them. --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  7. #7
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autofilter (Excel 2000)

    I've been using the following code:

    Selection.AutoFilter Field:=a, Criteria1:=

Posting Permissions

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