-5

What I'm trying to achieve is to insert number of new rows after certain rows which contain in column A text "rich", and if column B in same row contains value less then 10 insert 2 rows after this row. But if column B in same row contain value higher then insert 1 row after this row. I'm not the best in writing loop code. I will appreciate any help.

eurano
  • 55
  • 1
  • 12
  • What have you tried yourself? You can look for help here: http://stackoverflow.com/questions/1463236/loop-through-each-row-of-a-range-in-excel and here: http://stackoverflow.com/questions/15816883/excel-vba-inserting-blank-row-and-shifting-cells – Wouter Nov 10 '16 at 08:35

1 Answers1

2

I managed to do it after some time :)

Sub macro1()
    Range("A1").Select
    ' remember the last row that contains a value
    Dim LastRow As Integer
    Dim CurrentRow As Integer
    LastRow = Range("A1").End(xlDown).Row
    CurrentRow = 1

    ' keep on incrementing the current row until we are
    ' past the last row
    Do While CurrentRow <= LastRow

        ' if the desired string is found, insert a row above
        ' the current row
        ' That also means, our last row is going to be one more
        ' row down
        ' And that also means, we must double-increment our current
        ' row
        If Range("A" & CurrentRow).Value = "rich" And Range("B" & CurrentRow).Value > 10 Then
            Range("A" & CurrentRow + 1).EntireRow.Insert xlIp
            Range("A" & CurrentRow + 1).EntireRow.Insert xlIp
            LastRow = LastRow + 2
            CurrentRow = CurrentRow + 1
        ElseIf Range("A" & CurrentRow).Value = "rich" And Range("B" & CurrentRow) < 10 Then
            Range("A" & CurrentRow + 1).EntireRow.Insert xlUp
            LastRow = LastRow + 1
            CurrentRow = CurrentRow + 1
        End If

        ' put the pointer to the next row we want to evaluate
        CurrentRow = CurrentRow + 1

    Loop

End Sub
eurano
  • 55
  • 1
  • 12