Home SpecialCells(xlCellTypeVisible) not working in UDF
Reply: 0

SpecialCells(xlCellTypeVisible) not working in UDF

user1708
1#
user1708 Published in April 25, 2018, 12:16 pm

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
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.574134 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO