You are here:

Excel/damon-ostrander's Vis code


Hello, I am referring to a posting from 2002 "COUNTIF that doesn't count hidden cells"
I as the others am trying to use conditional formatting based only on the visible data on a separate tab.
I found Damon's VBA code below, but not being a VB person I'm not sure how to apply it.
What I did was to create a new module and pasted the code as shown, however now my sheet won't open, it just hangs.
Can you tell me how I would apply this code to my sheet or provide an alternative solution.
Thank you!

Function Vis(Rin As Range) As Range
'Returns the subset of Rin that is visible
Dim Cell As Range
Set Vis = Nothing
For Each Cell In Rin
If Not (Cell.EntireRow.Hidden Or Cell.EntireColumn.Hidden) Then
If Vis Is Nothing Then
Set Vis = Cell
Set Vis = Union(Vis, Cell)
End If
End If
Next Cell
End Function

Function COUNTIFv(Rin As Range, Condition As Variant) As Long
'Same as Excel COUNTIF worksheet function, except does not count
'cells that are hidden
Dim A As Range
Dim Csum As Long
Csum = 0
For Each A In Vis(Rin).Areas
Csum = Csum + WorksheetFunction.CountIf(A, Condition)
Next A
End Function

What you have described is the correct method of applying it, and the function should work as written - POSSIBLY you have a lot of data which is causing the function to hang?  In my test sheet with minimal data it functions correctly (I would not have THOUGHT this would be especially memory hungry but anything is possible).

I'm happy to take a look at your sheet if that would help - my direct email is (you didn't originally ask me the question, but I found it in the question pool indicating the original expert was unable or unwilling to answer)
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Aidan Heritage


I have provided first hand support since `95 for Microsoft Office majoring in Word and Excel - support for all versions from 2 onwards. I'm based in the UK, so please allow for time differences when asking me questions from other parts of the world!


My background is in the insurance industry and call centre areas, but have been called upon to provide many varied solutions.

I'm educated to UK A level standard, but as I left school some 30 years ago that is rather irrelevent - university of life has provided more of a background!

©2017 All rights reserved.