Set Range of Visible Cells

I

iamito

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
 
G

Gary''s Student

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top