Auto numbering a column in Excel

[banglong]

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.

 

[maabadi]

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
	A	B	C	D	E	F	G
1		Nubmering	Data		New Numbering
2		1	aa		1
3		5	ee		5
4		2	bb		2
5
6		7	gg		7
7		8	hh		8
8		3	cc		3
9		9	ii		9
10		6	ff		6
11
12		4	dd		4
13
14
15
16
17
18
19
20
Sheet2

Cell Formulas
Range	Formula
B2:B20	B2	=E2
E2:E20	E2	=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:

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)

 

[banglong]

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
	A	B	C	D	E	F	G
1		Nubmering	Data		New Numbering
2		1	aa		1
3		5	ee		5
4		2	bb		2
5
6		7	gg		7
7		8	hh		8
8		3	cc		3
9		9	ii		9
10		6	ff		6
11
12		4	dd		4
13
14
15
16
17
18
19
20
Sheet2

Cell Formulas
Range	Formula
B2:B20	B2	=E2
E2:E20	E2	=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:

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]

I write code based your example file. If your data is in other columns, you shuold change code.

 

[banglong]

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]

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]

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

 

[maabadi]

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:

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]

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:

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

 

[maabadi]

You're Welcome & Thanks for feedback.