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.
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