Auto numbering a column in Excel

banglong

New Member
Joined
Mar 11, 2016
Messages
17
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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
    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.

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
2,316
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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
Joined
Mar 11, 2016
Messages
17
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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.
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
2,316
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
I write code based your example file. If your data is in other columns, you shuold change code.
 

banglong

New Member
Joined
Mar 11, 2016
Messages
17
Office Version
  1. 365
  2. 2019
Platform
  1. Windows

ADVERTISEMENT

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.
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
2,316
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Please describe better.
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
Joined
Mar 11, 2016
Messages
17
Office Version
  1. 365
  2. 2019
Platform
  1. Windows

ADVERTISEMENT

Please describe better.
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:

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
2,316
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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
 
Solution

banglong

New Member
Joined
Mar 11, 2016
Messages
17
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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...
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top