CODEWITHSUNDEEP
excelvba
Lure
Lure

Reputation: 33

Excel Issue with CountIf with visible cells only

I have an issue with VBA Script. I'm trying to use CountIf function with filtering

Sub test31()
Debug.Print "Sum Visible Cells only: " & Application.WorksheetFunction.Sum(Sheets("Sheet1").Range("A2:A645").SpecialCells(xlCellTypeVisible))
 If Application.WorksheetFunction.CountIf(Sheets("Sheet1").Range("A2:A645").SpecialCells(xlCellTypeVisible), 1) > 0 Then
 Debug.Print "Ok"
 End If
End Sub

Example

Question: When I mark in filter (for example, please see attached image) first, third, fourth, fifth, etc. (but if I unmark some of number between this scope I am getting error "unable to get countif property of the worksheetfunction class" (but when I mark everything or mark first 2 or 3, etc (without unmarking like in image) error does not appear)

The file

Upvotes: 2

Views: 3463

Answers (2)

Gareth
Gareth

Reputation: 199

Lure Second attempt at answering your question.

First you are using a worksheet function countif in your vba in the form

    worksheetfunction.countif( rng , 1 )

and rng is required to be a single range.

Next you are using the methods Sheets("Sheet1").Range("A2:A645").SpecialCells(xlCellTypeVisible) specifically SpecialCells, which is returning a range, but the range is not a single continguous block. Instead, if you debug through your code, and put a watch on this range, then you see that the property Areas actually has a count of 2.

Set rng = Sheets("Sheet1").Range("b2:b645").SpecialCells(xlCellTypeVisible)
Set areaCount = rng.Areas.Count

So the problem is not with your CountIf method, but rather that the Range you are passing into CountIf is made up of different ranges, ie it is of the wrong type.

CountIf can therefore not work for you in this scenario. What you need to use is a worksheet function that allows for working with filtered datasets.

So the function Subtotal which does take a list of ranges seems to be a more appropriate WorksheetFunction for your needs. Now you need to choose an appropriate Aggregate method for the first parameter, which for you is 2 or 3. Count numbers only : 2 or count none blank cells : 3.

Usefully the Aggregate function provides a list of aggregations you can use.

You can now also use the Aggregate function or Subtotal function to Sum your visible ranges, by using the Aggregate function Sum : 9.

Putting all of this together here is a suggested code snippet for you..

Sub test31()

    Dim rngToUse As Range
    Dim visibleSum As Long
    Dim countOfVisible As Long

    Set rngToUse = Sheets("Sheet1").Range("b2:b645")
    visibleSum = WorksheetFunction.Subtotal(9, rngToUse)
    countOfVisible = WorksheetFunction.Subtotal(3, rngToUse)

    Debug.Print "Sum Visible Cells only: " & visibleSum
    Debug.Print "Count of Visible Cells : " & countOfVisible

    If countOfVisible > 0 Then
        Debug.Print "Ok"
    End If

End Sub

I hope that was a little more useful and informative.

Regards Gareth

Upvotes: 1

micstr
micstr

Reputation: 5206

COUNTIF does not seem to like non-contiguous blocks which will happen when you get the list filters and rows disappear. But this I mean ticking 2 means range of visible is A1: A2 and A10:A645.

You need to consider using another function.

COUNTA will count non blanks. So we ask it to count the number of non blanks in the visible range. Note we make a range object as it makes the code easier to read and later parameterise.

NEW CODE

Sub test31()
 Dim rng As Range

 Set rng = ThisWorkbook.Sheets("Sheet1").Range("A2:A645")

 Debug.Print "Sum Visible Cells only: " & Application.WorksheetFunction.Sum(rng.SpecialCells(xlCellTypeVisible))

 ' to see non contiguous uncomment this next line
 ' rng.SpecialCells(xlCellTypeVisible).Select
 If Excel.WorksheetFunction.CountA(rng.SpecialCells(xlCellTypeVisible)) > 0 Then
   Debug.Print "Ok"
   Debug.Print "number visible: " & Excel.WorksheetFunction.CountA(rng.SpecialCells(xlCellTypeVisible))
 End If

End Sub

OUTPUT

testing all in filter

Sum Visible Cells only: 3337
Ok
number visible: 643

testing with 2 unselected

Sum Visible Cells only: 3323
Ok
number visible: 636

I am not sure of your desired output but hope this helps.

Upvotes: 1

Related Questions