0

enter image description here

Above is what I want my result to look like. You can see that when the number on the far left column skips from 85 to 87, and form 89 to 91, one blank row was inserted. I want that to happen as many times as the difference is between the rows if the difference is > 1. So if the number on the far left went from 85 to 90, it would insert 5 blank rows. And this is occurring between columns D:G.

This is code I took from another post here on stackoverflow but I am not sure how to store the difference of the rows as a variable and then insert that many blank rows.

ActiveSheet.Cells(4, 2).Activate

While ActiveCell.Value <> ""

If ActiveCell.Value - ActiveCell.Offset(-1, 0).Value < 0 Then

    ActiveCell.EntireRow.insert shift:=xlShiftDown

Else

    ActiveCell.Offset(1, 0).Activate

End If

Wend
LibertyWolf
  • 27
  • 5

1 Answers1

1

No need to Select/Activate anything. And always loop backwards when inserting or deleting rows otherwise you risk skipping rows.

Amended to cater for column C.

Sub x()

Dim r As Long, n As Long

For r = Range("C" & Rows.Count).End(xlUp).Row To 2 Step -1
    n = Cells(r, "C").Value - Cells(r - 1, "C").Value
    If n > 1 Then
        Cells(r, "C").Resize(n - 1).EntireRow.Insert shift:=xlShiftDown
    End If
Next r

End Sub

Before

enter image description here

After

enter image description here

SJR
  • 22,986
  • 6
  • 18
  • 26
  • I like how you set this up, thank you. But This did not work for me, It inserted the blank rows all at the top instead of between the rows. – LibertyWolf Dec 21 '20 at 16:24
  • It works so probably needs adjusting for your precise layout, – SJR Dec 21 '20 at 16:42
  • I realized that my columns actually started on C, not D. So I changed your code to be 3 instead of 4 and it works much better now. But now its inserting blank rows based on the difference between the last row and first row, in this case its 100 rows. So now I have 100 blank rows on top. I need to play with it more and figure out why it's doing that. – LibertyWolf Dec 21 '20 at 17:17
  • Have added a screenshot, it works for me. Did you also change the `Range("D" & Rows.Count)` to C? – SJR Dec 21 '20 at 17:27
  • Thank you for the screen shot. Yes I did change that to C. In my worksheet, the cells start in C2:G2. Maybe that could cause an issue? It looks like yours starts in the first row. – LibertyWolf Dec 21 '20 at 17:35
  • Post a screenshot, that shouldn't make a difference. – SJR Dec 21 '20 at 17:39
  • I figured it out, I changed For r = Range("C" & Rows.Count).End(xlUp).Row To 2 Step -1 To: For r = Range("C" & Rows.Count).End(xlUp).Row To 3 Step -1 and now it works perfectly. Thank you! – LibertyWolf Dec 21 '20 at 17:47
  • Glad you sorted it, I didn't envisage that causing your problem. – SJR Dec 21 '20 at 17:49
  • Would there be a way to make it so the row that gets inserted is only in columns C:G, instead of the entire row? – LibertyWolf Dec 21 '20 at 20:27
  • Try `Cells(r, "C").Resize(n - 1,5).Insert shift:=xlShiftDown`. – SJR Dec 21 '20 at 20:30