Home SpecialCells(xlCellTypeVisible) not working in UDF

# SpecialCells(xlCellTypeVisible) not working in UDF

CLR
1#
CLR Published in 2017-04-05 14:38:18Z
 Based on the question posed by @Chips Ahoy, I decided to create a UDF to find the PercentRank of visible cells in a range. While @Chips seems happy with my syntax correction, I am actually unable to get my UDF to work correctly. When I run the below, the two addresses output read identical. In my example using a formula of =VisiblePercentRank($A$2:$A$41,0.5) , both addresses output to the immediate window read $A$2:$A$41, despite rows 3 to 11 being hidden by an autofilter. Code: Function VisiblePercentRank(x As Range, RankVal As Double) Debug.Print x.Address, x.Rows.SpecialCells(xlCellTypeVisible).Address VisiblePercentRank = WorksheetFunction.PercentRank(x.Rows.SpecialCells(xlCellTypeVisible), RankVal) End Function  Also tried removing .Rows: Function VisiblePercentRank(x As Range, RankVal As Double) Debug.Print x.Address, x.SpecialCells(xlCellTypeVisible).Address VisiblePercentRank = WorksheetFunction.PercentRank(x.SpecialCells(xlCellTypeVisible), RankVal) End Function  Should the second output not read $A$2,$A$12:$A$41 or have I missed something? Using Excel/Office 2013, 64bit on Win7, 64bit. BRAIN FRYING UPDATE I have found that my UDF works if I run it from the immediate window: ?VisiblePercentRank(range("A2:A41"),0.5) $A$2:$A$41 $A$2:$A$11,$A$39:$A$41 0.207  But if run from an in-cell formula of =VisiblePercentRank(A2:A41,0.5): $A$2:$A$41 $A$2:$A$41 
CallumDA
2#
 It seems that SpecialCells is known to fail in UDFs. A few sources: 1, 2, 3 You'd have to create your own function. Perhaps something like this: Function VisiblePercentRank(x As Range, RankVal As Double) Debug.Print x.Address, VisibleCells(x).Address VisiblePercentRank = WorksheetFunction.PercentRank(VisibleCells(x), RankVal) End Function Private Function VisibleCells(rng As Range) As Range Dim r As Range For Each r In rng If r.EntireRow.Hidden = False Then If VisibleCells Is Nothing Then Set VisibleCells = r Else Set VisibleCells = Union(VisibleCells, r) End If End If Next r End Function 
 You need to login account before you can post.
Processed in 0.433051 second(s) , Gzip On .