Counting non-blank cells in only visible columns

AlisonHS

New Member
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).

Alison

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Fluff

MrExcel MVP, Moderator
Hi & welcome to MrExcel.
VBA Code:
`` If Cell.EntireColumn.Hidden = False And LEN(TRIM(Cell))=0 Then``

AlisonHS

New Member
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

MrExcel MVP, Moderator
Use
VBA Code:
`` SumVisCols = SumVisCols + 1``

AlisonHS

New Member
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

MrExcel MVP, Moderator
You're welcome & thanks for the feedback.

Replies
1
Views
131
Replies
3
Views
173
Replies
6
Views
163
Replies
3
Views
352
Replies
3
Views
212

1,128,180
Messages
5,629,188
Members
416,370
Latest member
Lgathana

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back