# Excel Assign Row Number If Cell Is Not Blank

In this post we will see how to assign row number if we have data available for that particular row i.e. assign sequence number if the value in the adjacent cell is not blank.

Suppose we have excel data as shown in the below picture and we want to assign row number to below excel rows if we have non blank data in column B. Row 2 in column A should be equal to 1, row 3 should be equal to 2 and row 4 would be blank as B4 doesn’t have value in it so we will not assign row number to it as it is null.

Step 1

Open VB editor from Developer tab in excel as shown in the figure. Step 2

Insert a module in which we will write a function that will assign row number to the row if the value is not blank. Step 3

Now double click the newly created module to open that and paste the below code in the module.

Sub AssignRowNumber()

Dim sh As Worksheet, lastR As Long, arr, arrA, count As Long, i As Long

Set sh = ActiveSheet

lastR = sh.Range(“B” & sh.Rows.count).End(xlUp).Row: count = 1

If lastR <= 2 Then Exit Sub

arr = sh.Range(“B2:B” & lastR).Value ‘place the range in an array for faster iteration

arrA = sh.Range(“A2:A” & lastR).Value

For i = 1 To UBound(arr)

If arr(i, 1) <> “” Then arrA(i, 1) = count: count = count + 1

Next i

sh.Range(“A2”).Resize(UBound(arrA), 1).Value = arrA

End Sub Step 4

Run the code by pressing F5 or by pressing play button in VB editor ad you will see that row numbers have been assigned to all non bank cells as shown in the picture below. Hope this helped.

Share The Knowledge
• 