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.

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



--
Regards
Frank Kabel
Frankfurt, Germany


MikeT wrote:
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.

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.



-----Original Message-----
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



--
Regards
Frank Kabel
Frankfurt, Germany


MikeT wrote:
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.

.

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
-------------------

Change the 1000 to the number of rows you have and change the one afte
the comma in cells(i-1, 1) and cells(i,1) to the column number.



--
Message posted from http://www.ExcelForum.com

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

"MikeT" wrote in message ...
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.


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.655 / Virus Database: 420 - Release Date: 08/04/2004