Posts by J.Con

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

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

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.

forum.ozgrid.com/index.php?attachment/69450/

Here is a snippet.

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).

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?