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

--
Regards,

OssieMac