Worksheet function & SpecialCells(xlCellTypeVisible)?

[Charley Kyd]

This function works as expected when I call it from a subroutine. But when I
call it as a worksheet function, it always returns rngCur no matter how many
rows are hidden.

Public Function VISIBLE(rngCur As Range) As Range
Application.Volatile
Set VISIBLE = rngCur.SpecialCells(xlCellTypeVisible)
End Function

SpecialCells isn't causing an action. Why isn't it working? Can anyone
suggest a workaround?

Thanks.

Charley

 

[Tim Zych]

SpecialCells performs a type of selection and triggers the
Worksheet_SelectionChange event under certain circumstances. Guess it can't
be used with UDFs for that reason.

Here's one possible workaround:

Public Function VISIBLE(rngCur As Range) As Range
Dim rngRow As Range, rngCol As Range
Dim rngVisRow As Range, rngVisCol As Range
Dim cell As Range, rngVis As Range
Application.Volatile
For Each rngRow In rngCur.Rows
If rngRow.EntireRow.Hidden = False Then
If rngVisRow Is Nothing Then
Set rngVisRow = rngRow
Else
Set rngVisRow = _
Union(rngRow, rngVisRow)
End If
End If
Next rngRow
For Each rngCol In rngCur.Columns
If rngCol.EntireColumn.Hidden = False Then
If rngVisCol Is Nothing Then
Set rngVisCol = rngCol
Else
Set rngVisCol = _
Union(rngCol, rngVisCol)
End If
End If
Next rngCol
For Each cell In rngVisRow.Cells
If Not Application.Intersect( _
cell, rngVisCol) Is Nothing Then
If rngVis Is Nothing Then
Set rngVis = cell
Else
Set rngVis = Union(cell, rngVis)
End If
End If
Next cell
If Not rngVis Is Nothing Then
Set VISIBLE = rngVis
Else
Set VISIBLE = Nothing
End If
End Function

 

[Charley Kyd]

Thanks, Tim.

I was trying to avoid looping, because it slows performance significantly.
When I do loop, I use a slightly different logic. It's based on the idea
that the condition I'm looking for tends to exist in clumps of rows, rather
than in isolated rows. Therefore, we get better performance if we act on all
contiguous rows in one operation, rather than acting on each row one at a
time.

In this instance, my tests show that for each row in a contiguous range it's
about 300 times faster to merely note that fact rather than to apply the
Union method. To illustrate, here's the general approach I'll probably take:

Dim bInHiddenBlock as boolean
Dim rngStart as range
Dim rngCur as range
Dim rngViz as range

bInHiddenBlock =false
Set rngViz = nothing

For Each rngCur in whatever
If this row is hidden
If not bInHiddenBlock then
bInHiddenBlock =true
Set rngStart = rngCur
End if
Else
If bInHiddenBlock then
bInHiddenBlock =false
If rngViz is nothing then
set rngViz = Range(rngStart,rngCur.Offset(-1,0))
else
set rngViz = Union(rngViz,
Range(rngStart,rngCur.Offset(-1,0)))
End if
End if
Next whatever

''If we end with a block of rows to hide...
If bInHiddenBlock then
set rngCur = last cell in looping range
If rngViz is nothing then
set rngViz = Range(rngStart, rngCur)
else
set rngViz = Union(rngViz, Range(rngStart, rngCur))
End if
end if

By the way, one could use the Iif function here rather than If-Else-EndIf.
But my tests show that If-Else-EndIf is about five times faster than Iif.

All the best,

Charley