# Add 1 to Col B based on the rank of Col A values

#### MrSpark

##### New Member
I have problem may be someone could help. I have been using below code which add the Rank number to "ColB". This rank is based on that Rank number will be added to the highest value as 1 then to 2nd highest as 2 then 3rd highest as 3 and so on and last Rank number will be added to the value which is lowest in Rank.

Here is my code which is working perfectly but it does not skip the 0 . I want to skip the 0 that rank should not count 0 in "ColA".

Your help will be highly appreciated.

Here is attached Picture where it also includes the 0. My code.

VBA Code:
``````Sub WriteRanks()
Dim Rng         As Range
Dim Arr         As Variant
Dim Ct          As Long
Dim r           As Long

Application.ScreenUpdating = False
With Worksheets("Sheet1")

Set Rng = .Range(.Cells(2, "A"), .Cells(.Rows.Count, "A").End(xlUp))
Arr = Rng.Value
For r = 1 To UBound(Arr)

Arr(r, 1) = Arr(r, 1) + (r / (10 ^ 10))
Next r

With .UsedRange
Ct = .Column + .Columns.Count
End With
Set Rng = Rng.Offset(0, Ct)
Rng.Value = Arr

For r = 1 To UBound(Arr)
.Cells(r + 1, "B").Value = WorksheetFunction.Rank(Arr(r, 1), Rng, 0)
Next r

.Columns(Rng.Column).EntireColumn.Delete
End With
Application.ScreenUpdating = False
End Sub``````

### Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

#### navic

##### Active Member
How about with a formula (Rank descending)
Code:
``=IF(\$A2=0,"",IF(OR(\$A2>0,\$A2<0),RANK(\$A2,\$A\$2:\$A\$30)))``
Or (Rank ascending)
Code:
``=IF(\$A2=0,"",IF(\$A2>0,RANK(\$A2,\$A\$2:\$A\$30,1)-COUNTIF(\$A\$2:\$A\$30,0),RANK(\$A2,\$A\$2:\$A\$30,1)))``

• MrSpark

#### MrSpark

##### New Member
Hi thanks for the formula solution but looking to fix the problem through VBA.

#### navic​

##### Well-known Member
Try this code:
VBA Code:
``````Sub Macro1()
Dim Lr As Long, Rng As Range
Lr = Range("A" & Rows.Count).End(xlUp).Row
Set Rng = Range("B2:B" & Lr)
Range("B2").FormulaR1C1 = "=IF(RC[-1]=0,"""",COUNTIFS(R2C1:R22C1,""<>""&0,R2C1:R22C1,"">""&RC[-1]) +IF(RC[-1]>0, 1,0))"
Range("B2").AutoFill Destination:=Rng
Rng.Value = Rng.Value
End Sub``````

• MrSpark

#### MrSpark

Thank you

##### Well-known Member
You're Welcome & Thanks for Feedback.

Replies
4
Views
122
Replies
3
Views
117
Replies
1
Views
68
Replies
29
Views
1K
Replies
3
Views
142

### Forum statistics

1,141,130
Messages
5,704,454
Members
421,350
Latest member
jake9951 ### 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