Results 1 to 8 of 8
  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)

    When you run an autofilter it shows in the statusbar 15 of 400 records. Are there a way of access the value 15 through code?

    Thanks

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autofilter (Excel 2000)

    Yes of course. Oh, you wanted to know how? <g>

    Sub test()
    MsgBox ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisib le).Address
    End Sub

    Gives the address (including the header row) of the visible (filtered) cells.
    You can replace UsedRange with e.g. Range("A2100") to specify what area to look in (note I excluded the header row 1 in this example).
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    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)

    Thanks for the address of the cells.

    I use the autofilter to delete "unwanted" data lines. No I have a problem in the case it there are no data for a specific selection. That's why I would like to access the value as displayed in the status bar after the autofilter is selected (10 of 2750 records found)

    Thanks

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Autofilter (Excel 2000)

    Use SUBTOTAL function

    In a cell:
    =subtotal(3,Datarange)

    or in VB
    Rows = Application.WorksheetFunction.Subtotal(3, Datarange)

    The 3 in subtotal is for "counta". See subtotal in help for the other numbers (you can do min/max, average, stdev, etc) on the VISIBLE data with subtotal

    Steve

  5. #5
    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)

    Thanks. It is a shame that the statusbar values are not accessable in code

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autofilter (Excel 2000)

    That would be:

    ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisib le).Rows.Count
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Autofilter (Excel 2000)

    Jan Karel,

    When you apply a filter, the visible cells are probably not contiguous. In Excel 97, the Rows property of a range that consists of multiple areas, returns the rows of the first area only. To get a count of all rows, you need something like

    Dim lngCount As Long
    Dim rng As Range
    For Each rng In ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisib le).Areas
    lngCount = lngCount + rng.Rows.Count
    Next rng
    MsgBox lngCount

    Is this different in Excel 2000 and XP?

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autofilter (Excel 2000)

    Good point Hans! I didn't test this properly as shows.......
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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