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