Selected Answer
You are mixing two ideas in your code. One is to prepare a new line for new entries. The other is to apply numbering to already existing rows. The first you will need all the time but the second will be needed only once. Therefore it's not a good idea to mix the two ideas into one procedure.
However, before you start numbering you need a numbering method, and the method to use is to apply a number format to the cell. (Right-click > Format Cells > Numbers > Custom > Type). This is the format you should use:-
"numbering" 0
Now the word "numbering" is included in the cell format and all you need to enter is a number. In the attached workbook this is demonstrated in cell A5 where the number in the cell is 3 but the display is "numbering 3". You can change the format to "numbering 03" or "numbering 003" by adding more zeros to the format. You can also change the word to another language, for example.
The code below applies this numbering method to all existing rows in your worksheet. Note that it should be saved to a standard code module (like Module1). Observe that the display format will have 2 digits.
Sub SetNumbering()
' 250
Dim Rng As Range
With Worksheets("nu")
' from 3B to end of column B
Set Rng = .Range(.Cells(3, "B"), .Cells(.Rows.Count, "B").End(xlUp))
End With
With Rng.Offset(0, -1)
.NumberFormat = """numbering"" 00"
.Formula = "=ROW()-2"
.Copy
.PasteSpecial xlValues
End With
Application.CutCopyMode = False
End Sub
The serial number of each row is 2 less than the row number. That makes for a formula like = ROW() -2, and exactly that is used in the code. To modify the number change the formula, like ROW()-3 or ROW()-1.
Now, for adding a row I usually prefer to double-click. Normally, double-click enters Edit mode. But in the attached workbook, if you double-click on the first empty cell at the bottom of column A, a new line is added. It copies the formats from row 3 (which you can change in the code), including borders, applies the number format, writes the serial number and selects the adjacent cell. This code must be in the code module of the worksheet on which you want the action.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
' 250
Dim TriggerCell As Range
Dim R As Long
Set TriggerCell = Cells(Rows.Count, "A").End(xlUp).Offset(1)
With Target
If .Address = TriggerCell.Address Then
Application.EnableEvents = False
R = .Row
Rows(3).Copy ' copy formats from row 3
Rows(R).Insert Shift:=xlDown
On Error Resume Next ' error if no special cells
Rows(R).Cells.SpecialCells(xlCellTypeConstants).ClearContents
On Error GoTo 0
With Cells(R, 1)
.NumberFormat = """numbering"" 00"
.Value = R - 2 ' row number - 2
End With
Application.EnableEvents = True
.Offset(, 1).Select
End If
End With
End Sub
You are well advised to add Option Explicit at the top of every code module as you see in the attached workbook. It saves tons of time and effort.
