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.
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)
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)
COUNTIFv = Csum
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 firstname.lastname@example.org (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)
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