Insert a new row based upon an if/then command

[Guest]

I would like to insert a new blank row when there is a value in a certain
colum of our spreadsheet.

How can I insert the row automatically after the row where the condition is
satisfied.

for example.... =IF(a1=popcorn,yippie,"insert a blank row below a1...")

How the heck do I do that?

 

[Trevor Shuttleworth]

You can't ... unless you use VBA.

If you think about it, if you could do what you want, how would Excel know
when to stop inserting blank rows after the row that met the condition ?

If you run a macro to check the column(s) it can do whatever you want.

In your example, I'm not sure what the condition is that you want to test.
You are actually saying insert a blank line when A1 is not equal to
"popcorn"

Maybe you meant:

=IF(A1="popcorn","insert a blank row below a1...","yippie")

The code to check all cells in column A might look something like:

Sub test()
Dim i as Long
For i = Range("A65536").End(xlUp).Row To 1 Step -1
If Range("A" & i).Value = "popcorn" Then
Range("A" & i).Offset(1, 0).EntireRow.Insert
End If
Next
End Sub


Regards

Trevor