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

  • Count Number Of Weekdays From Date Column In Excel

    This post demonstrates how to calculate number of weekdays in a given date values in excel. Suppose we have a […]

    Share The Knowledge
  • Find Trailing Space In Excel

    There are situation when we try to look up some value in excel but we do not get the desired […]

    Share The Knowledge
  • VBA To Open Latest Excel Workbook

    vba to open latest excel workbook in a folder, how to open most recent file from a folder in excel vba, open latest file from folder in excel vba, open most recent workbook in excel, code to open the most recent file from a folder in vba

    Share The Knowledge
  • Add Prefix/Suffix To A Text/Value In Excel

    In this post we will see how we could add/prefix a string to each value in a column in excel […]

    Share The Knowledge