Adding numbers in sequence using VBA

[Cruiser69]

Hi all.

I use the code below to add missing numbers in a sequence.

It works well, but if there is a letter with a number, it fails

EG:
1
2
2A
3
5

Sub Add_In_Sequence()

  Dim LastRow As Long
Dim gap As Long
Dim i As Long, ii As Long

    Application.Screenupdating = False
    
    With ActiveSheet
    
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        For i = LastRow To 3 Step -1
        
            gap = .Cells(i, "A").Value - .Cells(i - 1, "A").Value
            If gap > 1 Then
            
                .Rows(i).Resize(gap - 1).Insert
            End If
        Next i
        
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        .Cells(3, "A").Value = .Cells(2, "A").Value + 1
        .Cells(2, "A").Resize(2).AutoFill .Cells(2, "A").Resize(LastRow - 1)
    End With
 
End Sub

It fails at this line

 gap = .Cells(i, "A").Value - .Cells(i - 1, "A").Value

Is there a way to modify this code to make it work with numbers and letters

Regards,

Graham

 

[oldbrewer]

2A is not a missing number, but do you want to delete it because you have a 2 and a 3 in your list ?

 

[Cruiser69]

No, 2A is meant to be there. 4 is missing