Count Non-Blank Cells with VBA

[10char]

Hi,

I have one page with a column that uses formulas to populate it from another worksheet. It uses Index-Match to populates the cells if the conditions are met and leaves it blank ("") if they are not. I would like to use VBA to count the number of cells in this range that have entries and then assign it to a variable.

Thanks!

 

[Scott Huish]

If it's not blank, is the result text or a number or both?

 

[Rick Rothstein]

I have one page with a column that uses formulas to populate it from another worksheet. It uses Index-Match to populates the cells if the conditions are met and leaves it blank ("") if they are not. I would like to use VBA to count the number of cells in this range that have entries and then assign it to a variable.

Assuming the column is Column A, something like this should work for you...

CountOfFilledCells = Evaluate("SUM(0+(A1:A" & Cells(Rows.Count, "A").End(xlUp).Row & "<>""""))")

 

[10char]

If it's not blank, is the result text or a number or both?

They are just text entries.
I tried y = WorksheetFunction.Worksheets("myWorksheet").CountA("myRange") where myWorksheet is the name of the worksheet and myRange is the range but it didn't work.

I'll try your suggestion now Rick. Thanks!

Edit - Just realized I can't specify a whole column, since my range starts at B20 and ends at B70. There's other information that is at the beginning of column B.

 

[Rick Rothstein]

I'll try your suggestion now Rick. Thanks!

Edit - Just realized I can't specify a whole column, since my range starts at B20 and ends at B70. There's other information that is at the beginning of column B.

Here is my code line modified to work within the range B20:B70...

CountOfFilledCells = Evaluate("SUM(0+(B20:B70<>""""))")

or, since the range is a fixed size, here is an alternative way to write it...

CountOfFilledCells = [SUM(0+(B20:B70<>""))]

 

[Jonmo1]

It might be easier to count the criteria the Index Match formula Used to populate the cells with....
Can you post that formula?

 

[10char]

Rick, how do I specify that the range is in another worksheet?

Here is the formula running from B20 to B70... =IF(ISERR(INDEX(Data!$H$6:$H$215, SMALL(IF(($E$17=Data!$Y$6:$Y$215)=IF($E$18=Data!$W$6:$W$215,TRUE,""), ROW($Y$6:$Y$215)-MIN(ROW($Y$6:$Y$215))+1, ""), ROW(A1)))),"", INDEX(Data!$H$6:$H$215, SMALL(IF(($E$17=Data!$Y$6:$Y$215)=IF($E$18=Data!$W$6:$W$215,TRUE,""), ROW($Y$6:$Y$215)-MIN(ROW($Y$6:$Y$215))+1, ""), ROW(A1))))

I don't even how it works anymore lol... Wrote it a while back.

I only just started with VBA yesterday.

 

[Rick Rothstein]

Rick, how do I specify that the range is in another worksheet?

Here is the formula running from B20 to B70... =IF(ISERR(INDEX(Data!$H$6:$H$215, SMALL(IF(($E$17=Data!$Y$6:$Y$215)=IF($E$18=Data!$W$6:$W$215,TRUE,""), ROW($Y$6:$Y$215)-MIN(ROW($Y$6:$Y$215))+1, ""), ROW(A1)))),"", INDEX(Data!$H$6:$H$215, SMALL(IF(($E$17=Data!$Y$6:$Y$215)=IF($E$18=Data!$W$6:$W$215,TRUE,""), ROW($Y$6:$Y$215)-MIN(ROW($Y$6:$Y$215))+1, ""), ROW(A1))))

Assuming the name of the worksheet is Data, first way...

CountOfFilledCells = Evaluate("SUM(0+([COLOR=#ff0000][B]Data![/B][/COLOR]B20:B70<>""""))")

Short way...

CountOfFilledCells = [SUM(0+([COLOR=#ff0000][B]Data![/B][/COLOR]B20:B70<>""))]

Note that you use the same "rule" as for including sheet names in formulas, so if the sheet name had a space in it, you would surround the sheet name with apostrophes.

 

[Jonmo1]

If I'm not mistaken, the countblank function counts formula blanks as blank.
Not sure why, and this baffles me..
But we can use that.

Since the range is B20:B70, that's 51 rows.

Try
MyCount = 51 - Application.Countblank(Sheets("Data").Range("B20:B70"))

If we want to avoid hard coding the 51, try

With Sheets("Data")
    MyCount = .Range("B20:B70").Rows.Count - Application.Countblank(.Range("B20:B70"))
End With

 

[10char]

Data is actually another worksheet.

I basically have 3. 1 with data, another with calculations and a 3rd which generates charts and stuff. I need this VBA code to check the range in worksheet 2 ("Raw"), get the number of non-blank/text cells in the range and then assign it to a variable, which I will then in turn use to do things to the 3rd sheet.