UDF returns different result in worksheet to that in VBA

[OssieMac]

I created a UDF to return the range/s of the visible cells in an AutoFiltered
range.

When I use the function in VBA it returns the correct result with the range
as an array of the visible cells like the following.
A14:G16,A22:G22,A27:G28,A31:G31,A34:G34

When I use the function on a worksheet it simply returns a range that is the
first and last cells of the unfiltered data in the entire Autofilter range
including the non visible cells like the following.
A2:G42

Is this just an idiosyncrasy of Excel or am I missing something here.

Sub Test_FilterRnge()
MsgBox FilterRnge(, 0)
End Sub



Function FilterRnge(Optional strWs As String = "", _
Optional bolAbs As Boolean = False) As String

Dim ws As Worksheet
Dim FilterVisible As Range

If strWs = "" Then
Set ws = ActiveSheet
Else
Set ws = Sheets(strWs)
End If

If ws.AutoFilterMode Then 'Test if filter arrows present
If ws.FilterMode Then 'Test if actually filtered

With ws.AutoFilter.Range

'Next line returns number of visible cells divided _
by number of columns in AutoFilter range. _
If greater than 1 then some data is visible. _
Equal to 1 then only column headers visible.
If .SpecialCells(xlCellTypeVisible).Count / _
.Columns.Count > 1 Then

'Assign the filtered range/s to a VBA variable _
No Column Headers; Data only. _
(Column numbers can be omitted in _
Offset and Resize functions.)
Set FilterVisible = .Offset(1, 0) _
.Resize(.Rows.Count - 1, .Columns.Count) _
.SpecialCells(xlCellTypeVisible)
End If

End With

End If

End If

If Not FilterVisible Is Nothing Then
FilterRnge = FilterVisible.Address(bolAbs, bolAbs)
Else
FilterRnge = "Error!"
End If

End Function

 

[Peter T]

Unfortunately SpecialCells does not work with a UDF called from a cell
formula.
Look at the Hidden property of rows in the filter range.

Regards,
Peter T

 

[OssieMac]

Hi Peter,

I give up. Where do I find this or some documentation on what you are
referring to?
"Look at the Hidden property of rows in the filter range."

 

[Gary''s Student]

Look at this simple function:

Function see_able(inputt As Range) As String
Dim rr As Range, r As Range
see_able = ""
Set rr = Intersect(inputt, ActiveSheet.UsedRange)
For Each r In rr
If Rows(r.Row).Hidden = False Then
If see_able = "" Then
see_able = r.Address
Else
see_able = see_able & "," & r.Address
End If
End If
Next
End Function


It does not use SpecialCells. It just loops thru the cells in the input
range and determines if they are in a visible row or not. So if rows 5 thru
7 are hidden, then:

=see_able(C111)

will display:

$C$1,$D$1,$C$2,$D$2,$C$3,$D$3,$C$4,$D$4,$C$8,$D$8,$C$9,$D$9,$C$10,$D$10,$C$11,$D$11

in the worksheet.

 

[OssieMac]

Thanks to both of you for the answers. A picture is worth a 1000 words.

To Peter,

I appreciate your taking the time to answer and the first part of your
answer confirms
to me that it is is just an idiosyncrasy of Excel.

I completely misunderstood what you were referring to by "Look at the Hidden
property of rows in the filter range". I thought that you meant that it would
explain all about the first part of your answer and I couldn't make the
connection.

I am now assuming that Gary's Student correctly interpretted what you meant
and it now makes sense.

By the way at this point in time I don't need it as a worksheet function and
can't even think of a use for it as a worksheet function at the moment. I
only want the VBA to copy filtered results and decided to come up with
something generic. I just had the function in a cell during testing.

 

[Peter T]

Try this and adapt to your needs -

Function VisFilterRows(Optional ws As Worksheet, _
Optional bUseAddress As Boolean) As String
Dim n As Long
Dim s As String, t1 As String, t2 As String
Dim rFlt As Range
Dim rw As Range

On Error Resume Next
If ws Is Nothing Then Set ws = Application.Caller.Parent
On Error GoTo errH
If ws Is Nothing Then Set ws = ActiveSheet

Set rFlt = ws.AutoFilter.Range

For Each rw In rFlt.Rows

If Not rw.Hidden Then
If bUseAddress Then
t2 = rw.Address(0, 0)
Else
t2 = rw.Row
End If
If Len(t1) = 0 Then
t1 = t2
End If
ElseIf Len(t2) Then
If Len(t1) Then
If t1 <> t2 Then
t1 = t1 & ":" & t2
End If
s = s & t1 & ","
t1 = "": t2 = ""
End If
End If
Next

If Len(t1) Then
If t1 <> t2 Then
t1 = t1 & ":" & t2
End If
s = s & t1 & ","
t1 = "": t2 = ""
End If

n = Len(s)
If n Then s = Left$(s, n - 1)
VisFilterRows = s

Exit Function

errH:
VisFilterRows = "err:" & Err.Number
End Function

Regards,
Peter T

 

[Peter T]

That demo is not quite right if using bUseAddress=True and the filter range
is 2+ columns. Easy to fix but as I doubt anyone will ever want it just
remove the option altogether.

Peter T