带有仅包含可见单元格的CountIf的Excel问题 [英] Excel Issue with CountIf with visible cells only

问题描述

我在使用VBA脚本时遇到问题.我正在尝试使用 CountIf 函数进行过滤

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

示例

问题:当我在过滤器中进行标记时(例如，请参见所附的图像)，第一，第三，第四，第五等(但是，如果我取消标记此范围之间的一些数字，则会收到错误消息无法获取worksheetfunction类的countif属性")(但是当我标记所有内容或标记前2或3等(没有像图像一样未标记)时，不会出现错误)

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)

文件

推荐答案

COUNTIF 似乎不喜欢非连续的块，当您获得列表过滤器并且行消失时会发生这种情况.但是，我的意思是打勾2表示可见范围是 A1:A2 和 A10:A645 .

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 将计算非空白.因此，我们要求它计算可见范围内的非空白数量.请注意，我们创建了一个range对象，因为它使代码更易于阅读和以后进行参数化.

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.

新代码

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

输出

测试所有过滤器

Sum Visible Cells only: 3337
Ok
number visible: 643

使用2个未选中项进行测试

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.

这篇关于带有仅包含可见单元格的CountIf的Excel问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！