You are here:

Excel/damon-ostrander's Vis code

Advertisement


Question
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
Application.Volatile
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
Else
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
COUNTIFv = Csum
End Function

Answer
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 aidan.heritage@virgin.net (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

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Aidan Heritage

Expertise

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!

Experience

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

Education/Credentials
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!

©2016 About.com. All rights reserved.