How to Rank Filtered Cells with duplicates without skipping numbers in excel vba

[mobi124]

Hello I Need your help on an Excel Vba problem How to Rank Filtered Cells with duplicates without skipping number in excel vba Suppose I have worksheet that contains subject marks for different classes. I want them filtered and then show filtered rank. The problem I am unable to get rank when duplicate occurs. I want the duplicates be the same rank but then the next rank. but it skips rank e.g: 1,2,3,3,5,6 but I want 1,2,3,3,4,5, The same formula is given below {=SUM(IF(SUBTOTAL(103,OFFSET($N$8:$N$70,ROW($N$8:$N$70)-ROW($N$8),0,1))>0,IF(N8<$N$8:$N$70,1)))+1} I Have got another instance of the above formula which doesn't skip number but shows different results like 190, 180, 557. {=SUM(IF(SUBTOTAL(3,OFFSET($N$8:$N$108,ROW($N$8:$N$108)-ROW($N$8),0,1))>0,SUMPRODUCT((N8 < $N$8:$N$108)/COUNTIF(N$8:N$108,N$8:N$108))))+1}