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.
