Check value and insert row

[undemane]

I have data in a worksheet (say row1, row 2, row 3, etc.), and column D has a number in it. I want to check that number. If it is 0 to 6, do nothing; if it is between 6 & 12, I want to insert a blank row (row #2); if it is between 13 & 18, then add two rows (row2, row3), and so on. What formula should I use? Macro?

 

[venkat1926]

'quote
'I have data in a worksheet (say row1, row 2, row 3,
'etc.), and column D has a number in it.
'I want to check that number. If it is 0 to 6,
'do nothing; if it is between 6 & 12,
'I want to insert a blank row (row #2);
'if it is between 13 & 18, then add
'two rows (row2, row3), and so on. What formula
'should I use? Macro?
'unquote

'SUPPOSE YOU WANT TO FIND THE VALUE 5 IN COLUMN D
' I PRESUME THAT 5 OCCURS ONLY ONCE IN COLUMN D
'CHANGE THE VALULE 5 TO YOUR REQUIREMNT . IF THIS IS STRING
'ENCLOSE IT WITHIN DOUBLE QUOTES
'POST FEEDBACK.

IMPORTANT: if you want to recheck the macro you have to first remove the blank rows and then run th macro

Sub test()
Dim k As Integer
Dim cfind As Range
With Columns("D:D")
Set cfind = .Cells.Find(what:=5, lookat:=xlWhole)
If cfind Is Nothing Then GoTo line1
'MsgBox cfind.Row
If cfind.Row <= 6 Then GoTo line1
k = cfind.Row / 6
'MsgBox k
Range(Cells(2, "a"), Cells(k + 1, "a")).EntireRow.Insert
GoTo line1
End With
line1:
End Sub

 

[mikerickson]

I think this will do what you want

Sub test()
Dim insertCount As Long
Dim i As Long
With ThisWorkbook.Sheets("sheet1")
    For i = .Cells(.Rows.Count, 4).End(xlUp).Row To 1 Step -1
        With .Cells(i, 4)
            insertCount = Int((Val(CStr(.Value)) - 1) / 6)
            If 0 < insertCount Then
                .Offset(1, 0).Resize(insertCount, 1).EntireRow.Insert
            End If
        End With
    Next i
End With
End Sub