Counta

[billandrew]

Evening all

Unsure why the following is resulting in an incorrect value.



Cells(1,7).value = application.WorksheetFunction.CountA(Range("B:B")

 

[billandrew]

could I insert an offset somewhere to exclude a header row or simply minus 1 at the end

 

[jproffer]

Not sure about that one, but it would be easier to just exclude it by definition....

Cells(1,7).value = application.WorksheetFunction.CountA(Range("B2:B100000") 'or however far down you  have to go

It's no less efficient either way. Right now it's evaluating a few over a million cells...any less is just a nanosecond more efficient

And, it's here-nor-there now, but I think I had my threads mixed up while ago. Calculation manual/auto wouldn't have anything to do with that line of code working or not.

 

[billandrew]

Thank You!

 

[billandrew]

I'm definitely losing my self. Below is an excerpt of code provided by, (I believe) the great Mr. Rothstein.

This answers my original need.

Cells(1, 7).Value = Columns("B").SpecialCells(xlConstants).Count - 1