I seem to be 'search challenged' today - I'm sure this is easy. How does one count the number of non-blank cells in a range using VBA?
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
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
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!