Inserting rows based on another cells value

[MikeT]

I have a column of cells that could have the value of
either 1, 2 or 3.

I want 1 row added below the cell if the value is 2, 2
rows added below the cell if the value is 3 and no cells
added if the value is 1.

Anyone know if this is possible and if it is how is it
done.

 

[Frank Kabel]

Hi Mike
try the following:

Sub insert_rows()
Dim lastrow As Long
Dim row_index As Long

lastrow = ActiveSheet.Cells(Rows.count, "A").End(xlUp).row
For row_index = lastrow - 1 To 1 Step -1
with Cells(row_index, "A")
If .Value =2 or .value = 3 then
Cells(row_index + 1, "A").resize(.value-1,1).EntireRow.Insert
(xlShiftDown)
End If
end with
Next
End Sub

 

[kkknie]

A quick macro:

Code
-------------------
Sub test()

Dim i As Long

For i = 2 To 1000
If Cells(i - 1, 1).Value = 2 Then Cells(i, 1).EntireRow.Insert shift:=xlDown
If Cells(i - 1, 1).Value = 3 Then
Cells(i, 1).EntireRow.Insert shift:=xlDown
Cells(i, 1).EntireRow.Insert shift:=xlDown
End If
Next

End Su

 

[MikeT]

Thanks Frank,

Here's more of my work you might want to do for me.

In the rows that are inserted, can I copy the data from
certain cells(not the whole row, but constant certain
cells) in the row above that caused the insert.

Thanks for your help

MikeT

P.S. KKKNIE, I couldn't get your macro to run.

 

[Robert McCurdy]

Hi Mike, to get the values repeated for the inserted row select the column after this macro. (not the whole column)


Sub MoreRows()
Dim Rng As Excel.Range, i As Long
Set Rng = Selection
Application.ScreenUpdating = False
ActiveCell.Select
With Rng
For i = .Cells.Count To 1 Step -1
If .Cells(i, 1).Value > 1 And _
.Cells(i, 1).Value < 4 Then _
.Rows(i + 1).Resize(.Cells(i, 1).Value - _
1, 1).EntireRow.Insert shift:=xlDown
Application.StatusBar = "Inserting rows at row " & i
Next
End With
End Sub

Now press F5 > Special > Blanks > OK > = > arrow up > Ctrl + Enter


Regards Robert