Macro to add missing numbers in a sequence

Hello,

I have a long list of alphanumeric values (i.e. BB235) that is comprised of multiple value sequences. For example, one sequence may be C3345 to C3399 and then the sequence will jump to JB900 to JB979. I would like to design an excel macro that would automatically insert rows and the missing values in a sequence based on rows that I highlight.

Any tips or advice would be much appreciated, even if it is only to tell me that it cannot be done.

can I confirm - if say you selected in cell A1 value BB235, and in cell A2 value BB245, you would like to insert 9 rows between them and add the values BB236 to BB244

also - does every alphanumeric value beging with letters and end with numbers?

.. if so try this

it will only work if you have two cells selected and both cells' contents beging with the same letters (e.g. MM22 MM45, not MA22 MM45).

Sub fill_rows()
Dim val1 As String, val2 As String, txt1 As String, xNum As Long, xNum2 As Long, RowNum As Long
'establish where letters end and numbers begin in each cell value
xNum = 1
Do While Not IsNumeric(Mid(Selection.Cells(1).Value, xNum, 1))
    xNum = xNum + 1
Loop
xNum2 = 1
Do While Not IsNumeric(Mid(Selection.Cells(2).Value, xNum2, 1))
    xNum2 = xNum2 + 1
Loop

'establish that both selected cells beging with the same letters
txt1 = Left(val1, xNum - 1)
If Left(val2, xNum - 1) < Left(val1, xNum2 - 1) Then
    MsgBox "selected values do not begin with the same letters"
    End
End If
'establish the rows to insert
val1 = Right(Selection.Cells(1).Value, Len(Selection.Cells(1).Value) - xNum + 1)
val2 = Right(Selection.Cells(2).Value, Len(Selection.Cells(2).Value) - xNum + 1)

'Insert Rows
Selection.Cells(1).Select
For RowNum = val1 + 1 To val2 - 1
    ActiveCell.Offset(1, 0).EntireRow.Insert
    ActiveCell.Offset(1, 0).Select
    ActiveCell.Value = txt1 & RowNum
Next RowNum
End Sub

Thank you so much NickyC. FYI, when I run this, it inserts only the numbers of the missing sequences, not the text and the numbers. For example, if the sequence skips from RR54 to RR57, the macro will insert rows and the numbers
Do you know how I could adjust this so that the text would be inserted as well?

apologies for not replying sooner, I have been away.

This might solve your problem:

Sub fill_rows()
Dim val1 As String, val2 As String, txt1 As String, txt2 As String, xNum As Long, xNum2 As Long, RowNum As Long
'establish where letters end and numbers begin in each cell value
xNum = 1
Do While Not IsNumeric(Mid(Selection.Cells(1).Value, xNum, 1))
    xNum = xNum + 1
Loop
xNum2 = 1
Do While Not IsNumeric(Mid(Selection.Cells(2).Value, xNum2, 1))
    xNum2 = xNum2 + 1
Loop
val1 = Selection.Cells(1).Value
val2 = Selection.Cells(2).Value

'establish that both selected cells beging with the same letters
txt1 = Left(val1, xNum - 1)
txt2 = Left(val2, xNum2 - 1)
If txt1 <> txt2 Then
    MsgBox "selected values do not begin with the same letters"
    End
End If
'establish the rows to insert
val1 = Right(Selection.Cells(1).Value, Len(Selection.Cells(1).Value) - xNum + 1)
val2 = Right(Selection.Cells(2).Value, Len(Selection.Cells(2).Value) - xNum + 1)

'Insert Rows
Selection.Cells(1).Select
For RowNum = val1 + 1 To val2 - 1
    ActiveCell.Offset(1, 0).EntireRow.Insert
    ActiveCell.Offset(1, 0).Select
    ActiveCell.Value = txt1 & RowNum
Next RowNum
End Sub