VBA to insert rows above a negative number

[jaygil]

Hello,

I am trying to write a macro but it is beyond my skills at the moment. I want to be able to search a specific row in my spreadsheet to find any negative numbers, if a negative number is found I then want to insert 3 rows above the row that the negative number is found in.

any advice or help would be appreciated, thanks

Examples of the data and the desired result is attached below.

 

[Alex Blakenburg]

Does your data contain formulas and/or formatting ?
Using an array will be faster but it will lose those features if they exist in your spreadsheet.

 

[Michael M]

MAybe this way, depending on the questions stated by @Alex Blakenburg

Sub MM1()
Dim lr As Long, r As Long, lc As Integer, c As Integer
lr = Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row
lc = Cells.Find("*", , xlValues, , xlByColumns, xlPrevious).Column
For r = lr To 3 Step -1
    For Cell = 1 To lc
        If Cells(r, Cell).Value < 0 Then
            Range(Rows(r), Rows(r + 2)).Insert
            Exit For
        End If
    Next Cell
Next r
End Sub

 

[Alex Blakenburg]

@jaygil - can you also confirm that you are only using Column G to "to find any negative numbers,"

 

[jaygil]

Thanks for the responses, I will attempt that one Michael.

There is important formulas in the spreadsheet already and also more to come. The usual spreadsheet I will apply this to is usually much longer and also has further steps after this process.

Was just trying to break it down because i was stuck at this stage and continue to develop further.

 

[jaygil]

@jaygil - can you also confirm that you are only using Column G to "to find any negative numbers,"

Yep any negative numbers in column G

 

[Michael M]

I'm not in Excel at the moment.
But the code provided can be shortened AND speeded up considerably if the criteria is limited to col G.
@Alex Blakenburg might step in and modify to suit

 

[Alex Blakenburg]

Here is @Michael M's solution modified so that in only checks Column G for the negative per your post#6 clarification.

Sub MM1_mod()
Dim lr As Long, r As Long, lc As Integer, c As Integer
lr = Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row
lc = Cells.Find("*", , xlValues, , xlByColumns, xlPrevious).Column
For r = lr To 3 Step -1
    If Cells(r, "G").Value < 0 Then
        Range(Rows(r), Rows(r + 2)).Insert
    End If
Next r
End Sub

 

[Michael M]

@Alex Blakenburg
You can also delete the lc variable and its declaration

 

[jaygil]

Thanks again, I'm not on excel right now either but that looks logical to me I'll try it again first thing tomorrow