Insert row where sequential value is missing, including first and last values.

    http://stackoverflow.com/quest…alue-is-missing-excel-vba


    I posted this question of Stack Overflow but didn't find a solution.
    I am trying to add a blank row where a sequential value is missing.
    The following code works perfectly when the numbers are missing WITHIN the sequence (for example 0,1,2, ,4,5).


    Code
    Sub test() 
Dim i As Long, x, r As Range 
For i = Range("b" & Rows.Count).End(xlUp).Row To 2 Step -1 
    x = Mid$(Cells(i, "b"), 2) - Mid$(Cells(i - 1, "b"), 2) 
    If x > 1 Then 
        Rows(i).Resize(x - 1).Insert 
        Cells(i - 1, "b").AutoFill Cells(i - 1, "b").Resize(x), 2 
    End If 
Next


    But if either the first value (eg. 0) or last value (eg. 5) is missing, a blank row will not be added.
    Any ideas?

    Re: Insert row where sequential value is missing, including first and last values.


    Can you attach a workbook of what your data looks like?

    Re: Insert row where sequential value is missing, including first and last values.


    Your code refers to sequential numbers in column B but you sample sheet has Text in column B.


    Which column of numbers needs to be sequential? Also does the sequence need to be 1 to 9 repeatedly for the entire data set?

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

    Re: Insert row where sequential value is missing, including first and last values.


    Sorry, I edited the spread sheet. The sequential numbers are in column B, and yes, 1-9 repeatedly.
    Thanks

    Re: Insert row where sequential value is missing, including first and last values.


    Try this code on the original excel..


    Re: Insert row where sequential value is missing, including first and last values.


    It worked!!!!!!!! I CANNOT thank you enough!! :) :)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login