Hi all,
I'm trying to either use formulas or write a VBA function that will count all non-blank cells (blank or containing one space) in only visible (not hidden) columns. I have thoughts on each of these two pieces, but can't figure out how to make these work together.
I found this function that works well for summing visible columns only (but not counting):
Function SumVisCols(Rng As Range)
Dim Cell As Range
Application.Volatile
For Each Cell In Rng
If Cell.EntireColumn.Hidden = False And IsNumeric(Cell) Then
SumVisCols = SumVisCols + Cell
End If
Next Cell
End Function
And I have this formula that works for counting non-blank cells (but in all columns:
=SUMPRODUCT((TRIM(C3:C13)<>"")*1)
Basically I'm trying to figure out a way to take that formula and put it into the VBA above to make a function that applies this formula to only visible columns (the columns vs. rows distinction is important - can't use SUBTOTAL because of that).
Thanks for your help!
Alison
I'm trying to either use formulas or write a VBA function that will count all non-blank cells (blank or containing one space) in only visible (not hidden) columns. I have thoughts on each of these two pieces, but can't figure out how to make these work together.
I found this function that works well for summing visible columns only (but not counting):
Function SumVisCols(Rng As Range)
Dim Cell As Range
Application.Volatile
For Each Cell In Rng
If Cell.EntireColumn.Hidden = False And IsNumeric(Cell) Then
SumVisCols = SumVisCols + Cell
End If
Next Cell
End Function
And I have this formula that works for counting non-blank cells (but in all columns:
=SUMPRODUCT((TRIM(C3:C13)<>"")*1)
Basically I'm trying to figure out a way to take that formula and put it into the VBA above to make a function that applies this formula to only visible columns (the columns vs. rows distinction is important - can't use SUBTOTAL because of that).
Thanks for your help!
Alison