Inserting Rows

[pinkpanther6666]

All

In Column C starting at C1 and going downwards i have the numbers 1 to 100 but some numbers are missing

ie

1
2
3
5
6
7
8
9
15
19
23
56
89
99
100


Could somebody help me write some code to insert the missing rows and that row number in Column C



Many thanks in advance



Steve

 

[MrKowz]

Try this out:

Public Sub FixRowNumbers()
Dim i   As Long, _
    LR  As Long
    
Application.ScreenUpdating = False
LR = Range("C" & Rows.Count).End(xlUp).Row
For i = LR To 2 Step -1
    Do Until Range("C" & i - 1).Value = Range("C" & i).Value - 1
        Rows(i).Insert Shift:=xlDown
        Range("C" & i).Value = Range("C" & i + 1).Value - 1
    Loop
Next i
Application.ScreenUpdating = True
End Sub

 

[JackDanIce]

Try:

Sub turtleofdoom()

Dim i As Integer
 
With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With
 
For i = 1 To 100

    If Range("C" & i) <> i Then
        Rows(i).Insert
        Range("C" & i) = i
    End If

Next i
 
With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
End With
 
End Sub

 

[pinkpanther6666]

All,


Many thanks for your quick replies, i will try them in a bit but all looks good


Many thanks again



Steve