Set Range of Visible Cells
 

Hi

I need a UDF (user defined function) that extracts the visible cells
from a user defined range and gives a range of visible cells.

I found this UDF on the internet. But it gives me some errors when I
apply an AutoFilter to my table.

Where's the mistake?
Thanks in advance.

================================================
Function Vis(VisibleRange As Range) As Range

Dim Cell As Range
Application.Volatile

Set Vis = Nothing

For Each Cell In VisibleRange

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

Set Range of Visible Cells
 

If you are using the UDF on the worksheet, consider getting the address of
the range instead, even if this is a temporary first step:

Function Visad(VisibleRange As Range) As String
..
..
..
If Vis is Nothing Then
Visad=""
Else
Visad=Vis.Address
End If
End Function

At least this way you can see what the function is producing


--
Gary''s Student - gsnu2007a


"iamito" wrote:

Hi

I need a UDF (user defined function) that extracts the visible cells
from a user defined range and gives a range of visible cells.

I found this UDF on the internet. But it gives me some errors when I
apply an AutoFilter to my table.

Where's the mistake?
Thanks in advance.

================================================
Function Vis(VisibleRange As Range) As Range

Dim Cell As Range
Application.Volatile

Set Vis = Nothing

For Each Cell In VisibleRange

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