I am trying to run the PercentRank function on automatic filtering so that it only works on visible cells. I tried to define a range object, i.e.:
Dim x As Range
x = ActiveSheet.Range("K1:K6027").Rows.SpecialCells(x1CellTypeVisible)
And then tried to use this range for the ie function:
PercentRank = WorksheetFunction.PercentRank(Range(x, *value to be ranked*) )
But this code returns the message "compilation error: argument is not optional." I guess this is due to the syntax of the percentrank function, which takes two arguments to set the range of values it controls, but I'm just not clear conceptually, and then about how to convert this to code. Any help would be greatly appreciated.
source
share