Selecting sequential numbers only

[Ronanm]

Hi

I was looking for a way of moving sequential numbers from Column A to Column B. I.E. If column A had numbers 1,3,6,8,9,10,11,12,15,17,19,20,21,24,28
Then numbers 9,10,11,12 and numbers 19,20,21 would be moved to the respective cell to the right in Column B?

 

[footoo]

Assuming a header in A1 and the numbers start in A2, put this in B2 and fill down :

=IF(OR(A2-1=A1,A2+1=A3),A2,"")

 

[Repush]

some VBA to do the trick:

Sub MoveSeq()
    Dim c As Range
    Dim MyRange As Range
    Set MyRange = Selection
    ReDim Move(MyRange.Count)
    For Each c In MyRange
        If c.Value = c.Offset(1, 0).Value - 1 Then
            c.Offset(0, 1).Value = c.Value
            c.ClearContents
            c.Offset(1, 1).Value = c.Offset(1, 0).Value
        Else
            If c.Offset(0, 1).Value = c.Offset(-1, 1).Value + 1 Then c.ClearContents
        End If
    Next c
End Sub

 

[Ronanm]

Thank you footoo. I meant to say VBA, but I used your logic in the formula to create the code

Sub Sequencing()
Dim cell As Object
Dim irow As Long
Dim LastRow As Long
LastRow = Cells(99999, 1).End(xlUp).Row
    irow = 1
        For Each cell In Range(Cells(2, 1), Cells(LastRow, 1))
        
            If (cell.Value - 1 = cell(irow - 1, 1) Or cell.Offset(1, 0).Value + 1 = cell(irow + 1, 1)) Then
                    cell.Offset(0, 1).Value = cell.Value
                Else
                    cell.Offset(0, 1).Value = ""
            End If
        Next cell
    irow = 1 + 1
End Sub

Thank you Repush for our code too

 

[footoo]

Or :

Sub Sequencing()
With Range([B2], Cells(Rows.Count, "A").End(xlUp)(1, 2))
    .Formula = "=IF(AND(A2-1<>A1,A2+1=A3),"""",IF(OR(A2-1=A1,A2+1=A3),A2,""""))"
    .Value = .Value
End With
End Sub

 

[Ronanm]

Cheers Footoo