Adjust numbers when deleting a row

[Belleke]

Hello
I am looking for a bit of code that adjust the numbers when I delete a row.
In column A, I have an ID number.
Ex
A3 = 1
A4 = 2
A5 = 3
A6 = 4
When I delete row 4 then the result should be
A3 = 1
A4 = 2
A5 = 3
And not
A3 = 1
A4 = 3
A5 = 4
I hope it is clear
Please advice

 

[vletm]

Something like this?

Sub ResetRowNums()
    Application.ScreenUpdating = False
    With ActiveSheet
        d_row = InputBox("Row num: ", "Delete", ActiveCell.Row)
        If d_row <> "" Then
            .Rows(d_row & ":" & d_row).Delete
            y = 2
            Do
                y = y + 1
                .Cells(y, 1) = y - 2
            Loop Until .Cells(y + 1, 1) = Empty
        End If
    End With
    Application.ScreenUpdating = True
End Sub

 

[Belleke]

Hello vletm,
Your code does exact what I want, but stupid me, I forgot to mention that it should fit in code on an userform, and the row selection is done by listbox selection.
This is the code

Private Sub CommandButton3_Click()
Dim rng As Range, fnd As Range
Dim smessage As String
Dim Ctrl As Control

Set rng = Blad2.Range("A3:A" & Blad2.Cells(Rows.Count, "A").End(xlUp).Row)
Set fnd = rng.Find(What:=UserForm1.TBid.Value, LookIn:=xlValues, LookAt:=xlWhole)
If Not fnd Is Nothing Then
    smessage = "Ingave wissen, ben je zeker" + "?"
    If MsgBox(smessage, vbQuestion + vbYesNo, _
              "Bevestig ingave verwijderen!") = vbYes Then
        Rows(fnd.Row).Delete

        Call UserForm_Initialize
  For Each Ctrl In Me.Controls
    If TypeName(Ctrl) = "TextBox" Then
        Ctrl.Value = ""
    End If
Next Ctrl
Me.TBid.Value = WorksheetFunction.Max(Worksheets("Klanten").Range("A3:A5000")) + 1
End If
End If
End Sub

Thank you

 

[vletm]

Do Yo know that 'Forgot' means 'just remember'?

You could add those green rows somewhere ... is it Blad2?

Dim y

Rows(fnd.Row).Delete
y = 2
Do
y = y + 1
Blad2.Cells(y, 1) = y - 2
Loop Until Blad2.Cells(y + 1, 1) = Empty
Call UserForm_Initialize

 

[Belleke]

Hello vletm,
Thank you, It works like a charm now.
Did not know this one

'Forgot' means 'just remember'

You are really a great teacher.
Problem is solved.
Have a nice week-end.