Count cells in a filtered range

Question

Navigation

#1 by (0 votes)

0

I am working on some Excel reports in which we need the amount of tickets that are not being attended (empty cells), according to other criteria.

Currently this is what I have in my code

gsh.Shapes("UnpickedCE").TextFrame.Characters.Text = WorksheetFunction.CountBlank(sh.Range("I8:I" & k + 7))

However, this tells me all the cells in the range, without the filter applied, and if I use .specialCells

gsh.Shapes("UnpickedCE").TextFrame.Characters.Text = WorksheetFunction.CountBlank(sh.Range("I8:I" & k + 7).SpecialCells(xlCellTypeVisible))

I get this error

unable to get the countblank property of the worksheetfunction class

Any idea how to do that count?

vba macros excel

asked by Rodrigo Jimenez 06.09.2018 в 18:08

source

1 answer

Add the potions returned by a for Duplicate Orders

score 0 · Answer 1

gsh.Shapes("UnpickedCE").TextFrame.Characters.Text = WorksheetFunction.CountA(sh.Range("I8:I" & k + 7).SpecialCells(xlCellTypeVisible))

This should help you. The CountBlank function counts the blank cells and I think there will not be any.

You must use a CONTARA(COUNTA) to tell you all the written cells, or a CONTARSI(COUNTIF) with the wildcard from * to take any written cell.