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!

 

[Rick Rothstein]

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.

I think I gave you enough information in Message #8 for you to be able to make that change on your own.

 

[Jonmo1]

Change Data to Raw in the codes that have been posted.
And make sure the range is correct, it's using B20:B70 as you posted earlier..

 

[10char]

Oh okay I didn't see those posts before. Thanks guys!

Edit - I used Rick's first method and it worked! Awesome. Appreciate the help guys.

Just for future reference, I was just wondering how I could go about learning this stuff myself?

I tried googling worksheet functions and came up with WorksheetFunction.Worksheets("Raw").CountA ("B20:B70") myself. How come that didn't work?

 

[Scott Huish]

Because COUNTA counts formula blanks ""

 

[Skybluekid]

Hi Rick,

Just the thing I was looking for. Thank you