CODEWITHSUNDEEP
excelvba
Chips Ahoy
Chips Ahoy

Reputation: 11

VBA function over visible range only

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.

Upvotes: 1

Views: 833

Answers (1)

CLR
CLR

Reputation: 12279

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*)

Upvotes: 2

Related Questions