insert blank row every time cell value is less than zero

[MechEng]

Hi guys,


I have many thousands of rows of data and I was wondering is it possible to create a macro or a piece of vba code to insert a blank row everytime the value in a cell is less than zero, the blank row should be above the less than zero cell.


This was the best I could come up with but no joy:

[pre]

Sub gaps()

For a = 1 To ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
If ActiveSheet.Cells(a, 1).Value < 0 Then
ActiveSheet.Rows(a + 1).Insert
a = a + 1
End If
Next a

End Sub

[/pre]
Hopefully someone here with more va experiance than me can help?


Thanks in advance for any help.


Here is the work book so you know what I'm talking about;


https://rapidshare.com/files/3197595994/Book2.xlsm


I have flagged the negative values in red using conditional formating to make it clearer and have inserted blank rows manualy for the 1st few times so you can see what I'm trying to do.

 

[Luke M]

You were very close. One of the tricks was to count backwards on your loop.

[pre]

Sub InsertGaps()
Dim LastRow As Integer
LastRow = Cells(Rows.Count, 1).End(xlUp).Row

Application.ScreenUpdating = False
'We count backward so as not to get screwed up by the inserted rows
For i = LastRow To 1 Step -1
If Cells(i, "a").Value < 0 Then
Cells(i, "A").EntireRow.Insert Shift:=xlDown 'Cell with value goes below new row
End If
Next i
Application.ScreenUpdating = True

End Sub

[/pre]