How to autonumber rows if adjacent cell not blank in Excel?
In Excel, using the fill handle to manually create a series of numbers is a common way to generate serial numbers or indexes for lists. However, there are often situations where you only want to number rows if a specific adjacent cell contains data. For example, you may wish to automatically generate row numbers in a list, but skip numbering where the adjacent data cells are empty. What’s more, you might expect these numbers to update instantly as data is entered or removed—providing an up-to-date sequence at all times without manual intervention.
Number rows if adjacent cell not blank automatically with formula
Number rows if adjacent cell not blank automatically with VBA code
Number rows if adjacent cell not blank automatically with formula
An efficient way to achieve dynamic row numbering based on adjacent cell values is to use an Excel formula. With this approach, row numbers will be displayed only when the adjacent cell contains a value. When you add or delete data in these cells, the numbering updates automatically to match. Here is a practical method you can use:
1. Select the cell where you want the numbering to begin (for example, A2 if your data starts in B2). Enter the following formula:
=IF(B2<>"",COUNTA($B$2:B2),"")2. Next, drag the fill handle down alongside your data to apply this formula to other rows. The numbering will automatically adjust, displaying numbers only for rows where data is present in column B.
If you experience issues where the numbers do not update as expected, confirm that the formula has been copied to all relevant rows, and that there are no merged cells or data validations interfering with your range. Remember, formulas rely on accurate referencing and can be disrupted by structural changes to the worksheet.
Number rows if adjacent cell not blank automatically with VBA code
For more advanced users, or in cases where you prefer not to apply formulas throughout your worksheet—or you need numbering to update even when pasting data, removing blocks, or making frequent changes—a VBA macro offers a compelling alternative. Using VBA, you can automatically update row numbers in one column any time the adjacent cell is edited, without the need for formulas. This is ideal for data entry forms, import logs, or task lists with frequent layout changes.
1. Press Alt + F11 to open the Visual Basic for Applications editor window. In the Project Explorer, locate your workbook, then double-click the relevant worksheet (e.g., “Sheet1”) under “Microsoft Excel Objects”.
2. In the code window that appears, paste the following code. This example assumes you want to number rows in column A based on whether column B is not blank; you can adjust references for your specific range if necessary:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim chk As Range
Set chk = Intersect(Target, Me.Columns("B"))
If chk Is Nothing Then Exit Sub
Application.EnableEvents = False
Call RenumberNonBlank(Me, "B", "A", 2)
Application.EnableEvents = True
End Sub
Sub RenumberNonBlank(ws As Worksheet, _
keyCol As String, _
numCol As String, _
firstDataRow As Long)
Dim lastRow As Long
Dim r As Long
Dim seq As Long
lastRow = ws.Cells(ws.Rows.Count, keyCol).End(xlUp).Row
seq = 1
For r = firstDataRow To lastRow
With ws
If Trim(.Cells(r, keyCol).Value) <> "" Then
.Cells(r, numCol).Value = seq
seq = seq + 1
Else
.Cells(r, numCol).ClearContents
End If
End With
Next r
End Sub
3. Save and close the VBA editor. Now, whenever you add, edit, or clear content in column B, column A will be renumbered immediately, reflecting the presence (or absence) of data. The sequence will shift up or down as you add or remove entries in column B.
Notes and Precautions: This macro should be placed specifically in the desired worksheet code window (not in a module or ThisWorkbook) for it to respond to cell edits. Also, ensure that macros are enabled in your Excel settings for the code to function. If your "data range" shifts to columns other than A and B, update the Set chk = Intersect(Target, Me.Columns("B")) and Call RenumberNonBlank(Me, "B", "A", 2) references accordingly.
Troubleshooting: If the numbering does not update, double-check that you’re editing the correct worksheet and that the code is placed in the appropriate worksheet code window. Also, ensure you have saved the workbook as a macro-enabled file (.xlsm). For unexpected errors, recheck that you have not altered the structure of your worksheet, such as merged cells or data in header rows.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in