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.

Excel showing sample data to assign row nnumber

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.

Open developer tab to write VBA to assign row number to non blank cells

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.

Insert a module in the VB editor

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




Code to automatically assign row number to non blank rows

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.

result showing row numbers have been assigned to non blanks row

Hope this helped.




Share The Knowledge

Random Posts