CODEWITHSUNDEEP
excelvba
HeadOverFeet
HeadOverFeet

Reputation: 788

RANK in the column for unknown number of rows

I am in the very beginning with VBA programming. I would like to ask you how to add a rank function to calculate rank for the data in the whole column. I have data in column B and I want to rank it in column A.

Now I can do it for 1 line (A2) but I want macro to continue until there is an empty row in column B and I do not want to type a strict range in the query because there is a possibility, that more rows will be added in the future.

Please, can you help me? You are the best! Thank you in advance

Upvotes: 0

Views: 371

Answers (1)

Gary's Student
Gary's Student

Reputation: 96781

Consider:

Sub dural()
  Dim N As Long
  N = Cells(Rows.Count, "B").End(xlUp).Row
  Range("A2:A" & N).Formula = "=rank(B2,B$2:B" & N & ")"
End Sub

enter image description here

Upvotes: 1

Related Questions