# Auto numbering a column in Excel

#### banglong

##### New Member
Hi, Need your help on how to make autonumbering in column on each new data inserted. The new data inserted can be above, middle and below the column, and each data has sequence numbering. The numbering for old data cannot be change when each time new data inserted. Please refer attachment below. Need your help either in formula or macro. Thanks in advanve.

#### Attachments

• Screenshot 2021-06-27 020436.png
15.9 KB · Views: 8

### Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

##### Well-known Member
A. Formula Method

1. Go to File, Option, Formulas and at the Part Calculation option , enable iterative calculation.
2. Also change Maximum Iterations to 3
3. Press OK.
Now use this Format at your file. (Column E is Helper Column)
Book1
ABCDEFG
1NubmeringDataNew Numbering
21aa1
35ee5
42bb2
5
67gg7
78hh8
83cc3
99ii9
106ff6
11
124dd4
13
14
15
16
17
18
19
20
Sheet2
Cell Formulas
RangeFormula
B2:B20B2=E2
E2:E20E2=IF(C2<>"",IF(B2="",COUNTA(\$C\$2:\$C\$20),B2),"")

B. VBA Method
Right Click on Sheet Name and Select View Code and Paste this:
VBA Code:
``````Private Sub Worksheet_Change(ByVal Target As Range)
Dim Lr As Long
Application.EnableEvents = False
Lr = Range("B" & Rows.Count).End(xlUp).Row
If Intersect(Target, Range("C2:C" & Lr)) Is Nothing Then Exit Sub
Range("B" & Target.Row).Value = Application.WorksheetFunction.CountA(Range("C2:C" & Lr))
Application.EnableEvents = False
End Sub``````
Save File as Macro-Enabled Workbook(.xlsm)

Last edited:

#### banglong

##### New Member
A. Formula Method

1. Go to File, Option, Formulas and at the Part Calculation option , enable iterative calculation.
2. Also change Maximum Iterations to 3
3. Press OK.
Now use this Format at your file. (Column E is Helper Column)
Book1
ABCDEFG
1NubmeringDataNew Numbering
21aa1
35ee5
42bb2
5
67gg7
78hh8
83cc3
99ii9
106ff6
11
124dd4
13
14
15
16
17
18
19
20
Sheet2
Cell Formulas
RangeFormula
B2:B20B2=E2
E2:E20E2=IF(C2<>"",IF(B2="",COUNTA(\$C\$2:\$C\$20),B2),"")

B. VBA Method
Right Click on Sheet Name and Select View Code and Paste this:
VBA Code:
``````Private Sub Worksheet_Change(ByVal Target As Range)
Dim Lr As Long
Application.EnableEvents = False
Lr = Range("B" & Rows.Count).End(xlUp).Row
If Intersect(Target, Range("C2:C" & Lr)) Is Nothing Then Exit Sub
Range("B" & Target.Row).Value = Application.WorksheetFunction.CountA(Range("C2:C" & Lr))
Application.EnableEvents = False
End Sub``````
Save File as Macro-Enabled Workbook(.xlsm)
It works using formula, thanks so much Mr. Maabadi. But vba cannot show numbering Mr.

##### Well-known Member
I write code based your example file. If your data is in other columns, you shuold change code.

#### banglong

##### New Member

I write code based your example file. If your data is in other columns, you shuold change code.
this code works only 1 time only to insert the new data with numbering..for next data the numbering not shown..i should close the workbook first and reopen again to insert new data with numbering. how this code can work without to close and reopen the workbook. i try to use formula for my file but it takes time for iteration because data more than 300. thanks.

##### Well-known Member
1. You have multiple set data that want numbering & want to code work also after pasting new set data
OR
2. You want numbering unnumbered data from up to down at the same time.
OR
3. ....

#### banglong

##### New Member

1. You have multiple set data that want numbering & want to code work also after pasting new set data
OR
2. You want numbering unnumbered data from up to down at the same time.
OR
3. ....
Yes no. 2...numbering at the same time at random cell (can be up, down and middle) in same column

Last edited:

##### Well-known Member
1. First Right Click on Sheet Name and Select View Code and Clear Previous code.
2. At VBA Window go to Insert Module and Paste this code:
VBA Code:
``````Sub AddNumbering()
Dim Lr As Long, i As Long
Lr = Range("C" & Rows.Count).End(xlUp).Row
For i = 2 To Lr
If Range("C" & i).Value <> "" And Range("B" & i).Value = "" Then
Range("B" & i).Value = Application.WorksheetFunction.Max(Range("B2:B" & Lr)) + 1
End If
Next i
End Sub``````

And Run it ( at excel window press ALT , W , M , V ) Then select addnumbering & Run it

#### banglong

##### New Member
1. First Right Click on Sheet Name and Select View Code and Clear Previous code.
2. At VBA Window go to Insert Module and Paste this code:
VBA Code:
``````Sub AddNumbering()
Dim Lr As Long, i As Long
Lr = Range("C" & Rows.Count).End(xlUp).Row
For i = 2 To Lr
If Range("C" & i).Value <> "" And Range("B" & i).Value = "" Then
Range("B" & i).Value = Application.WorksheetFunction.Max(Range("B2:B" & Lr)) + 1
End If
Next i
End Sub``````

And Run it ( at excel window press ALT , W , M , V ) Then select addnumbering & Run it
good job bro...thanks so much..im appreciate it so much...

##### Well-known Member
You're Welcome & Thanks for feedback.

Replies
0
Views
161
Replies
1
Views
260
Replies
5
Views
610
Replies
3
Views
117
Replies
1
Views
105

1,141,062
Messages
5,704,061
Members
421,325
Latest member
tapete86

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back