Counting non-blank cells in only visible columns

[AlisonHS]

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

 

[Fluff]

Hi & welcome to MrExcel.
How about

 If Cell.EntireColumn.Hidden = False And LEN(TRIM(Cell))=0 Then

 

[AlisonHS]

Thank you! I think that addition to the VBA would now work to count only non-hidden and non-blank cells. Now I just need to figure out how to revise it to count the cells rather than sum since they're text - not numbers. Any thoughts on that? Thanks!

Function SumVisCols(Rng As Range)
Dim Cell As Range
Application.Volatile
For Each Cell In Rng

If Cell.EntireColumn.Hidden = False And Len(Trim(Cell)) = 0 Then

SumVisCols = SumVisCols + Cell

End If
Next Cell
End Function

 

[Fluff]

Use

 SumVisCols = SumVisCols + 1

 

[AlisonHS]

Thank you!! I made one minor tweak to change "If Cell.EntireColumn.Hidden = False And Len(Trim(Cell)) = 0 Then" to "If Cell.EntireColumn.Hidden = False And Len(Trim(Cell)) > 0 Then" since it was doing the opposite of what I wanted. But the code below works now! For any others reading, SumVisCols now counts all cells in one row in columns that are not hidden, and among cells that are not blank or have only spaces.

Function SumVisCols(Rng As Range)
Dim Cell As Range
Application.Volatile
For Each Cell In Rng

If Cell.EntireColumn.Hidden = False And Len(Trim(Cell)) > 0 Then

SumVisCols = SumVisCols + 1
End If
Next Cell
End Function

 

[Fluff]

You're welcome & thanks for the feedback.