count non-blank cells in range w/VB

  • Re: count non-blank cells in range w/VB


    Thank you, that works! : D Thinking ahead to possible future use, is there a way to keep VBA from counting a cell that is blank but has a formula producing that empty value?

  • Re: count non-blank cells in range w/VB


    Ah, the notorious loop, what I was trying to get away from. This did the trick:

    Code
    WorksheetFunction.CountA(Range("A1:D45")) - WorksheetFunction.CountBlank(Range("A1:D45"))

    Of course, this only works if EVERY cell in the range has a formula in it. :exclamat:

  • Re: count non-blank cells in range w/VB


    Hmmm .. you gfot me playing a bit. The following work as Excel formulas, now if they can be converted to VBA.


    For non-blank cells.
    =SUMPRODUCT((LEN(A1:E15)>0)*1)


    For blank cells.
    =SUMPRODUCT((LEN(A1:E15)=0)*1)

  • Re: count non-blank cells in range w/VB


    Alas, it's going to become a variable that will be accessed later in the macro. I prefer not to have the data ever write to a cell. Pity!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!