VBA function in visible range only

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.

+1
source share
1 answer

Set x . , :

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

Source: https://habr.com/ru/post/1016335/


All Articles