VBA automatic row insertion

[fgasparb]

Hello all!

After a few years of following posts I've finally come across a problem that I can't solve without some bespoke help. So, here goes my first post

I came across some code which has helped me insert a single row above when a cell value is 0. Here it is:

<code>Sub BlankLine()

Dim Col As Variant
Dim BlankRows As Long
Dim LastRow As Long
Dim R As Long
Dim StartRow As Long

Col = "L"
StartRow = 1
BlankRows = 1

LastRow = Cells(Rows.Count, Col).End(xlUp).Row

Application.ScreenUpdating = False

With ActiveSheet
For R = LastRow To StartRow + 1 Step -1
If .Cells(R, Col) = "0" Then
.Cells(R, Col).EntireRow.Insert Shift:=xlDown
End If
Next R
End With
Application.ScreenUpdating = True

End Sub</code>
I am working on a spreadsheet that needs blank rows to be inserted whenever there is a gap in time, so i.e. 12:30, 12:31,12:33 - on the right of 12:33 I have a formula that will return a 1 minute difference (which I mark as 0 to match the code). As I am dealing with 500k plus rows each time, and so far I have only had 1 minute gaps it has all been OK. The problem is that now I have different time gaps (anywhere between 1 and 40 minute gaps). Would any of you kind souls help me tweak this code so that, if in column 'L', a cell contains a number (which will be the minutes difference), the respective amount of rows are inserted above?

I hope this makes sense, very happy to clarify if it isn't!

Thanks all in advance,
P

 

[sheetspread]

This can be done without a macro. Filter out the nonzeroes, then F5-special-visible cells only, insert rows, remove the filter.

 

[sheetspread]

tweak this code so that, if in column 'L', a cell contains a number (which will be the minutes difference), the respective amount of rows are inserted above?

Sorry, missed that part, try here:

INSERT Number of Rows based on Cell Value

 

[fgasparb]

Fantastic! Thank you very much sheetspread :D