# 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

#### 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