Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297

    Unhappy Unanswered: CountIf & AutoFilter & SpecialCells(xlCellTypeVisible)

    Hi guys,

    Pulling my hair out here, and for the last few hours.

    I can't get this to work for love nor money.

    Code:
    Dim rng As Range
    Set rng = ActiveSheet.AutoFilter.Range
    
    For Each rngCell2 In rng.Columns(5).SpecialCells(xlCellTypeVisible)
    
        If (Not (rngCell2 = curCountry))
    
            MsgBox Application.WorksheetFunction.CountIf( _
                   rng.Columns(5).SpecialCells(xlCellTypeVisible), rngCell2)
    
        end if
    
        curCountry = rngCell2
    
    next rngCell2
    What this should do is:

    For every item showing on the screen (works correctly)
    If the value is different from the last value then (works correctly)
    Display a message box that shows how many of that value exist in the FILTERED list. (doesn't work - infact, it terminates the script dead, without producing an error)

    If I change the MsgBox statement to remove the bit in bold red below, it works a treat, except it just counts everything in the filter list that matches rngCell2's value, not just those being shown by the filter...

    Code:
    MsgBox Application.WorksheetFunction.CountIf( _
        rng.Columns(5).SpecialCells(xlCellTypeVisible), rngCell2)
    I'd really appreciate any advice/solutions anyone can put forward, as this is driving me nuts.

    Thanks guys!
    Last edited by kez1304; 02-22-12 at 12:31.
    Looking for the perfect beer...

  2. #2
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    CountIf needs a contiguous range passed into its first parameter. You can test this yourself by playing around with some countif formulas in a worksheet. The problem with your code is that special cells is returning a non-contiguous range (such as E2, E5, E10), which countif can't handle.

    The formula to do this would be something like:

    =SUMPRODUCT(SUBTOTAL(3,OFFSET(E2,ROW(E2:E19)-ROW(E2),,1)),--(E2:E19=E2))

    where E2:E19 is the autofilter's range excluding headers and E2 holds the value you're checking for. You could translate that into VBA code and incorporate it in your loop (so E2, E5, E10 etc) but, to be honest, I think it would be pretty inefficient. Instead, if I were you, I'd load the visible values into an array and then loop through the array once to store the count of each distinct value and which values are different to the previous ones. With that information retrieved, I would then report the counts.

  3. #3
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    I took your advice and loaded an array, looping through it to get the results I wanted.

    Thanks a lot buddy.
    Looking for the perfect beer...

Posting Permissions

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