Counting non-blank cells in only visible columns

AlisonHS

New Member
Joined
Jun 6, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,682
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
VBA Code:
 If Cell.EntireColumn.Hidden = False And LEN(TRIM(Cell))=0 Then
 

AlisonHS

New Member
Joined
Jun 6, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
55,682
Office Version
  1. 365
Platform
  1. Windows
Use
VBA Code:
 SumVisCols = SumVisCols + 1
 

AlisonHS

New Member
Joined
Jun 6, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
55,682
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,864
Messages
5,627,338
Members
416,242
Latest member
Kas O

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top