Consulting

Results 1 to 17 of 17

Thread: Count only visible data after filtering

  1. #1
    VBAX Regular
    Joined
    Nov 2019
    Posts
    16
    Location

    Count only visible data after filtering

    Hi, what do I need to add to the below code to only count the visible data after filtering. I assume it is possibly .SpecialCells(xlCellTypeVisible) or similar after With Worksheet("calculations"), but I can't figure it out. Help appreciated.
    Sub Count_only_visible_data()
    With ActiveSheet
    .Range("E5").Value = Countv("Work", "WSCA1", "Processed")
    .Range("E6").Value = Countv("Work", "WSCA2", "Processed")
    .Range("E7").Value = Countv("Work", "SECA11", "Processed")
    .Range("E8").Value = Countv("Work", "SECA12", "Processed")
    .Range("E9").Value = Countv("Work", "SECA13", "Processed")
    .Range("E17").Value = Countv("Work", "NWCA5", "Processed")
    .Range("E18").Value = Countv("Work", "NWCA6A", "Processed")
    .Range("E19").Value = Countv("Work", "NWCA6B", "Processed")
    .Range("E20").Value = Countv("Work", "NWCA7", "Processed")
    .Range("E21").Value = Countv("Work", "NWCA8", "Processed")
    .Range("E22").Value = Countv("Work", "NWCA9", "Processed")
    .Range("E23").Value = Countv("Work", "NWCA10A", "Processed")
    .Range("E24").Value = Countv("Work", "NWCA10B", "Processed")
    .Range("E32").Value = Countv("Work", "WSCA3", "Processed")
    .Range("E33").Value = Countv("Work", "WSCA4", "Processed")
    .Range("E34").Value = Countv("Work", "SECA14", "Processed")
    .Range("E35").Value = Countv("Work", "SECA15", "Processed")
    .Range("E36").Value = Countv("Work", "SECA16", "Processed")
    End With
    End Sub
    Function Countv(v1 As String, v2 As String, Optional v3 As String = "") As Long
     Dim rng1 As Range, rng2 As Range, rng3 As Range
     With Sheets("calculations")
     
     Set rng1 = .Range("D7:D100000")
     Set rng2 = .Range("L7:L100000")
     Set rng3 = .Range("M7:M100000")
     If Len(v3) > 0 Then
     Countv = Application.CountIfs(rng1, v1, rng2, v2, rng3, v3)
     Else
     Countv = Application.CountIfs(rng1, v1, rng2, v2)
     End If
     End With
    End Function

  2. #2
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,007
    Location
    Hi & welcome to the forum. Untested but try
    Function Countv(v1 As String, v2 As String, Optional v3 As String = "") As Long
        Dim rng1 As Range, rng2 As Range, rng3 As Range
        With Sheets("calculations")
     
            Set rng1 = .Range("D7:D100000").SpecialCells(xlCellTypeVisible)
            Set rng2 = .Range("L7:L100000").SpecialCells(xlCellTypeVisible)
            Set rng3 = .Range("M7:M100000").SpecialCells(xlCellTypeVisible)
            If Len(v3) > 0 Then
                Countv = Application.CountIfs(rng1, v1, rng2, v2, rng3, v3)
            Else
                Countv = Application.CountIfs(rng1, v1, rng2, v2)
            End If
        End With
    End Function
    Semper in excretia sumus; solum profundum variat.

  3. #3
    VBAX Regular
    Joined
    Nov 2019
    Posts
    16
    Location
    Hi Paul, that didn't work unfortunately. I received Type Mismatch error below them on the first Countv row

  4. #4
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,007
    Location
    try
    WorksheetFunction.CountIfs
    instead of
    Application.CountIfs
    Semper in excretia sumus; solum profundum variat.

  5. #5
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,007
    Location
    Sorry, that probably won't change anything! Can you post you workbook?
    Semper in excretia sumus; solum profundum variat.

  6. #6
    VBAX Regular
    Joined
    Nov 2019
    Posts
    16
    Location
    Hi Paul, I have tried to attach it....not sure if it will work.
    Attached Files Attached Files

  7. #7
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,007
    Location
    Visible only test (1).xlsm

    It worked ok for me with my code in post #2

    I changed the output data to Sheet2 as it was going into the main data area.

    The results can be seen in the attached.
    Semper in excretia sumus; solum profundum variat.

  8. #8
    VBAX Regular
    Joined
    Nov 2019
    Posts
    16
    Location
    Hi Paul, it works unfiltered but when I apply a filter it gives Type Mismatch error on the first Countv = Application.CountIfs(rng1, v1, rng2, v2, rng3, v3) row.

  9. #9
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,007
    Location
    Visible only test (2).xlsm

    Hi.

    I've put a helper column which is 1 for row visible and 0 for hidden and added the condition to the countifs.
    Semper in excretia sumus; solum profundum variat.

  10. #10
    VBAX Regular
    Joined
    Nov 2019
    Posts
    16
    Location
    Thanks Paul, I see how I go with it, cheers.

  11. #11
    VBAX Regular
    Joined
    Nov 2019
    Posts
    16
    Location
    Thanks Paul, I see how I go with it, cheers.

  12. #12
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    What is the filtercriterion ?
    It can be part of any sumproduct formula.

  13. #13
    VBAX Regular
    Joined
    Nov 2019
    Posts
    16
    Location
    Hi snb, numerous...last month, this month or change from Processed to Submitted.

  14. #14
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,007
    Location
    Hi snb. I went for =IF(SUBTOTAL(2, T7)>0, 1, 0) rather than sumproduct for the visible helper.
    Semper in excretia sumus; solum profundum variat.

  15. #15
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    In that case I'd prefer a pivottable.

  16. #16
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,007
    Location
    I'll learn about Pivot Tables once I've finished your 'course' on Arrays. Love the simplistic & logical layout of the site, starting to understand the content now
    Semper in excretia sumus; solum profundum variat.

  17. #17
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    Thanks

Tags for this Thread

Posting Permissions

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