Paste array to sheet with filter applied

Hi all.

I wrote a udf to rank based on filtering... if visible, I consider it, if hidden, I note it. I bubble sort high to low, fill an array with rank 1 to count .... then I re-sort rank array based on original order. All done with arrays. I need to paste the rank back to the sheet - remember the sheet is filtered.

I initiate the rank sub based on a value on the sheet changes - on calculate... and I also initiate the rank sub based on a subtotal calculate event on the sheet to capture when the filter is changed. All works well.. BUT!

It seems that when I paste the rank back to the filtered sheet, it does not paste.. something to do with the filtering. If I loop thru the array, it works, but slowly..
Does not work properly... well, works if the filter is set to show the 1st row in the range

Please Login or Register  to view this content.

This works, but way to slowly...

Please Login or Register  to view this content.

Thanks for the help....
Steve
Harrisburg, PA

Hi.. File attached.

Set filter based on "Product Series" and recalculate Rank of visible rows

Please Login or Register  to view this content.

File attached...