VBA function over visible range only

Question

I'm trying to run the PercentRank function over an autofiltered spreadsheet such that it only runs over visible cells. I tried defining a range object ie:

Dim x As Range
x = ActiveSheet.Range("K1:K6027").Rows.SpecialCells(x1CellTypeVisible)

And then tried using that range the function ie:

PercentRank = WorksheetFunction.PercentRank(Range(x, *value to be ranked*) )

But that code returns a "compile error : argument not optional" message. I'm assuming it's due to the syntax of the percentrank function requiring two arguments to set yhe range of values it runs over, but I'm just not clear conceptually then on how to translate that into code. Any help would be much appreciated.

Answer 1

You need to Set x and use that. Also, your brackets need shifting around:

Set x = ActiveSheet.Range("K1:K6027").Rows.SpecialCells(xlCellTypeVisible)
MyPercentRank  = WorksheetFunction.PercentRank(x, *value to be ranked*)