count non-blank cells in range w/VB

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    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


    Technically, if a cell has a formula in it, it's not blank. You may need to cycle (loop) through each cell in the range and count it only if its LEN = 0.

    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


    If you are going to write the answer to a cell, you could use something like:

    Code
    Sub count()
Range("G1") = "=SumProduct((Len(A1:E15) = 0) * 1)"
End Sub

    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!

Register Yourself Login