xlCellTypeVisible - not working?

[whitehurst]

Can somebody please explain where my misunderstanding o
SpecialCells(xlCellTypeVisible) is?

Example, a completely new workbook:

1. Name range "A1:E10" as "test".
2. Insert the following macros:

Public Function total(rng As Range)
total = rng.Cells.Count
End Function

Public Function shown(rng As Range)
shown = rng.SpecialCells(xlCellTypeVisible).Cells.Count
End Function

3. Insert '=total(test)' and '=shown(test)' into 2 cells not in th
range.

4. Both should say 50. Now, start hiding rows/columns intersecting th
'test' range. Even when using Ctrl-Alt-F9, the shown count alway
equals 50 - why?! Hide all the cells in the range - still 50?! Why?

Does SpecialCells(xlCellTypeVisible) not do what I think it should?

Thanks

 

[Dave Peterson]

specialcells doesn't work if it's called from a function in a cell on a
worksheet.

 

[Guest]

Try shortening it

shown = rng.SpecialCells(xlCellTypeVisible).Count

 

[Guest]

sorry - that does not work.