# Simple Rank Macro

#### alocke

##### Board Regular
Hi,
Just a simple thing - If I want to Rank data in cells A1,A3,A5,A7 amongst one another and also cells A2,A4,A6,A8 too.

The code I'm doing is more complex than this, but if someone can tell me how to do this then I'll be sorted. I think.

Cheers,
Alex

### Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
To rank A1, A3, A5, and A7, and place their ranking in their corresponding cells in Column B, try...

Code:
``````[font=Verdana]    Range("B1, B3, B5, B7").Formula = "=RANK(A1,(\$A\$1,\$A\$3,\$A\$5,\$A\$7))"
[/font]``````

To rank A1, A3, A5, and A7, and place their ranking in their corresponding cells in Column B, try...

Code:
``````[FONT=Verdana]    Range("B1, B3, B5, B7").Formula = "=RANK(A1,(\$A\$1,\$A\$3,\$A\$5,\$A\$7))"
[/FONT]``````
I mean this works perfectly, but if I was to do it for 300 odd cells is there a way I could do it so that I wouldn't have to input it as each individual cell.

Where are your 300 odd cells located?

do you mean RANK all even rows with one ranking and all odd cells with another ranking ???

Hi guys by 'odd' I meant 300 cells in total. So by that I mean ranking cells A1,A3,A5...... A299 between one another and then A2,A4,A6....A300 between one another.

So instead of writing it as a range in which one has to write every single cell involved can you define the range?

See if this macro does what you want. Note that the results are not dynamic. That is, if the original data is changed, the macro would need to be run again (though this could be automated too).

I suggest that you test on a small dummy worksheet with much fewer rows so you can more easily check the results.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> RankAlternateRows()<br>    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, rws <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, lr <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> a, b<br>    <br>    <SPAN style="color:#00007F">Const</SPAN> DataCol <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "A"   <SPAN style="color:#007F00">'<- Column data is in</SPAN><br>    <SPAN style="color:#00007F">Const</SPAN> fr <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN> = 2            <SPAN style="color:#007F00">'<- First data row</SPAN><br>    <br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    Columns(DataCol).Offset(, 1).Resize(, 3).Insert<br>    lr = Cells(Rows.Count, DataCol).End(xlUp).Row<br>    rws = lr - fr + 1<br>    <br>    <SPAN style="color:#00007F">With</SPAN> Cells(fr, DataCol).Resize(rws)<br>        a = .Value<br>        b = .Value<br>        <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> rws - 1 <SPAN style="color:#00007F">Step</SPAN> 2<br>            a(i, 1) = vbNullString<br>            b(i + 1, 1) = vbNullString<br>        <SPAN style="color:#00007F">Next</SPAN> i<br>        <SPAN style="color:#00007F">If</SPAN> rws Mod 2 = 1 <SPAN style="color:#00007F">Then</SPAN> a(rws, 1) = vbNullString<br>        <br>        <SPAN style="color:#00007F">With</SPAN> .Offset(, 3)<br>            .Value = a<br>            <SPAN style="color:#00007F">With</SPAN> .Offset(, -1)<br>                .FormulaR1C1 = _<br>                    "=RANK(RC[1],R" & fr & "C[1]:R" & lr & "C[1])"<br>                .Value = .Value<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>            .Value = b<br>            <SPAN style="color:#00007F">With</SPAN> .Offset(, -2)<br>                .FormulaR1C1 = _<br>                    "=RANK(RC[2],R" & fr & "C[2]:R" & lr & "C[2])"<br>                .SpecialCells(xlFormulas, xlErrors).FormulaR1C1 = _<br>                    "=RC[1]"<br>                .Value = .Value<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>        Columns(DataCol).Offset(, 2).Resize(, 2).Delete<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

Here is my test data and results.

Excel Workbook
AB
1DataRank
252
372
435
581
652
744
891
972
1044
1135
12
13
Rank Alternate Rows (2)

Last edited:
Hi Peter,
Thank you so much for the reply - I'll test it out when I'm in the office tomorrow. And let you know.
Just two little things, my version has to be more complex in that:
- Instead of there being two ranges, there is going to be 42 each 200 cells apart and consisting of 300 total cells in each range. I'll just have to Dim more elements, i.e just build on what you've written.
- If I need to do a similar thing with a PercentRank, do I just switch Rank with PercentRank.
Thank you so much for your help,
Alex.

Hi Peter, the macro works - but it breaks down when I input negative values. Any ideas?
Thanks,
Alex

but it breaks down when I input negative values.
"breaks down" is a bit vague.
Can you be more specific.

This is what I got. What is wrong with it?

Excel Workbook
AB
1DataRank
251
3-73
4-24
581
651
7-85
8-5.25
9-73
1043
1132
12
Rank Alternate Rows (3)

Replies
14
Views
356
Replies
2
Views
124
Replies
1
Views
161
Replies
2
Views
88
Replies
3
Views
134

1,203,051
Messages
6,053,221
Members
444,648
Latest member
sinkuan85

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back