Missed out number in a column

[Vignesh Veerasamy]

Hi Gurus,

I need to insert blank rows in a running series in the row with the missed number & highlight the same with color.I have attached the excel of the same. Sample number that is not available in the list is 153318 ,my requirement is to insert such missing number & highlight the inserted number.

Pl ignore the search made that is 153318 not E153318.
how to do this?
__________________________________________________________________

Mod edit : post moved to appropriate forum …​

 

[chirayu]

Sub MissingRowAdder()

Dim UpVal, CurrVal, DownVal As Double

Range("B3").Select
Do Until IsEmpty(ActiveCell)
    UpVal = ActiveCell.Offset(-1, 0)
    CurrVal = ActiveCell
    DownVal = ActiveCell.Offset(1, 0)
   
    If CurrVal = (UpVal + 1) Then
        ActiveCell.Offset(1, 0).Select
    ElseIf CurrVal > (UpVal + 1) Then
        ActiveCell.EntireRow.Insert shift:=xlDown
        ActiveCell = ActiveCell.Offset(-1, 0) + 1
        ActiveCell.Interior.Color = RGB(251, 228, 213)
        ActiveCell.Offset(1, 0).Select
    Else
    End If
   
Loop


End Sub

 

[Vignesh Veerasamy]

Sub MissingRowAdder()

Dim UpVal, CurrVal, DownVal As Double

Range("B3").Select
Do Until IsEmpty(ActiveCell)
    UpVal = ActiveCell.Offset(-1, 0)
    CurrVal = ActiveCell
    DownVal = ActiveCell.Offset(1, 0)
  
    If CurrVal = (UpVal + 1) Then
        ActiveCell.Offset(1, 0).Select
    ElseIf CurrVal > (UpVal + 1) Then
        ActiveCell.EntireRow.Insert shift:=xlDown
        ActiveCell = ActiveCell.Offset(-1, 0) + 1
        ActiveCell.Interior.Color = RGB(251, 228, 213)
        ActiveCell.Offset(1, 0).Select
    Else
    End If
  
Loop


End Sub

Sub MissingRowAdder()

Dim UpVal, CurrVal, DownVal As Double

Range("B3").Select
Do Until IsEmpty(ActiveCell)
    UpVal = ActiveCell.Offset(-1, 0)
    CurrVal = ActiveCell
    DownVal = ActiveCell.Offset(1, 0)
  
    If CurrVal = (UpVal + 1) Then
        ActiveCell.Offset(1, 0).Select
    ElseIf CurrVal > (UpVal + 1) Then
        ActiveCell.EntireRow.Insert shift:=xlDown
        ActiveCell = ActiveCell.Offset(-1, 0) + 1
        ActiveCell.Interior.Color = RGB(251, 228, 213)
        ActiveCell.Offset(1, 0).Select
    Else
    End If
  
Loop


End Sub

@chirayu You are awesome.

 

[Marc L]

Hi !

Not bad chirayu !

Two points :
• desactivating ScreenUpdating runs faster & smoother.

• Excel model object does not need to select any object !
• Faster without any selection, so work directly with object
• not with Selection neither ActiveCell in a loop …

A demonstration :​

Sub Demo()
            Application.ScreenUpdating = False
With Sheet1
    For R& = .[B2].End(xlDown).Row To 3 Step -1
            V = .Cells(R, 2).Value
           D& = V - 1 - .Cells(R - 1, 2).Value
        If D Then
                 .Cells(R, 2).Resize(D).Insert xlShiftDown
            With .Cells(R, 2).Resize(D)
                 .Font.Bold = True
                     .Value = Evaluate(V & "+ROW(" & .Address & ")-" & R + D)
            End With
        End If
    Next
End With
            Application.ScreenUpdating = True
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

 

[Vignesh Veerasamy]

Hi !

Not bad chirayu !

Two points :
• desactivating ScreenUpdating runs faster & smoother.

• Excel model object does not need to select any object !
• Faster without any selection, so work directly with object
• not with Selection neither ActiveCell in a loop …

A demonstration :​

Sub Demo()
            Application.ScreenUpdating = False
With Sheet1
    For R& = .[B2].End(xlDown).Row To 3 Step -1
            V = .Cells(R, 2).Value
           D& = V - 1 - .Cells(R - 1, 2).Value
        If D Then
                 .Cells(R, 2).Resize(D).Insert xlShiftDown
            With .Cells(R, 2).Resize(D)
                 .Font.Bold = True
                     .Value = Evaluate(V & "+ROW(" & .Address & ")-" & R + D)
            End With
        End If
    Next
End With
            Application.ScreenUpdating = True
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

@Marc L it works charm & really a smooth run of macro doesnt let know the running screen.You are awesome.