Insert rows between existing data

**Tickla** · 09-20-2010, 12:52 AM

Hi there,

I am wanting to insert a whole new row between each row in the existing data (say A2:A10) - A10 being the last cell used in the column

On this new row I am wanting to insert some formulas such as in cell A3 =IF(A2>5,Yes,No).

I am then wanting this to loop through for all the cells in existing data range.

I have looked through the existing posts on this and am still a litttle stuck. I have been able to insert the spacing rows but not not insert the formula beneath.

Im not new to excel, but new to VBA. Any help would be kindly appreciated.

**JBeaucaire** · 09-20-2010, 03:12 AM

Try this:

Option Explicit

Sub InsertRowsAndFormula()
Dim LR As Long  'last row with data
Dim RW As Long

'Add blank rows
    LR = Range("A" & Rows.Count).End(xlUp).Row + 1
    For RW = LR To 2 Step -1
        Rows(RW).Insert xlShiftDown
    Next RW

'Add formulas
    LR = Range("A" & Rows.Count).End(xlUp).Row + 1
    Range("A2:A" & LR).SpecialCells(xlBlanks) _
        .FormulaR1C1 = "=IF(R[-1]C>5, ""yes"", ""no"")"
End Sub

**tenk283** · 09-20-2010, 03:30 AM


Private Sub Insert()

Dim row, count, column As Integer
Dim formula As String
' not sure which column you want, a=1, b=2 and so on.

column = 1 ' you specify

count = 1 ' or whichever row you want to start in

row = ActiveSheet.UsedRange.Rows.count
row = row * 2

formula = "=IF(A2>5,Yes,No)"

Do Until count = row
count = count + 1
Cells(count, column).EntireRow.Insert


If count = row Then

Exit Sub

End If

Cells(count, column).Select
Cells(count, column).formula = formula
count = count + 1



Loop

end sub

?

Bit of trouble with the loop... Should get you going in the right direction.