Insert Column when a sequential isn't found

**reggie1000** · 05-13-2013, 11:45 AM

Hello,

I have a problem, where i have a row of numbers and data underneath that are imported in they sit in row A and run to column 20, these numbers are sequential 1,2,3,4 etc, but there are sometimes that certain numbers are missing, what i need to do is to insert the missing number and column when they are missing.The missing numbers/column may be random but it always will finish up in the correct order when imported.

Hopefully it should be to much of a problem, i've attached a sample file to explain the problem better.

thanks reg

**AB33** · 05-13-2013, 12:32 PM

Try this code

Sub test()
    Dim i As Long, x, LC&
    On Error Resume Next
    Application.ScreenUpdating = 0
 With ActiveSheet
    LC = .Cells.Find("*", , , , xlByColumns, xlPrevious).Column

    For i = LC To 2 Step -1
        x = .Cells(1, i) - .Cells(1, i - 1)
        If x > 1 Then
            .Columns(i).Insert
            .Cells(1, i - 1).AutoFill .Cells(1, i - 1).Resize(, x), 2
        End If
    Next
 End With
 Application.ScreenUpdating = True
End Sub