Inserting rows
 

I am trying to write some code to insert a row above
whenever a "1" is found in Column A. For instance, if
a "1" is found in col A, row 10, I want to insert a row
above so that all cells are moved down. My problem is
figuring out how to tell it to select a particular row
when I don't know what the row number will be. I've
written the following code:

Dim rng As Range
Dim cell As Range

Set rng = Range(Cells(2, 1), Cells(Rows.Count, 1).End
(xlUp))
For Each cell In rng
If cell.Value = 1 Then
Rows(cell).Select
Selection.Insert Shift:=xlDown

End If
Next

the problem is that because the first cell is A2, it stops
and selects that row and inserts the line at that point,
regardless of whether the cell contains a "1", and doesn't
go on to the next cell in the range.

Inserting rows
 

Hi Lee

See my answer to Rex in the thread<Insert a row
6 threads above yours

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Lee" wrote in message ...
I am trying to write some code to insert a row above
whenever a "1" is found in Column A. For instance, if
a "1" is found in col A, row 10, I want to insert a row
above so that all cells are moved down. My problem is
figuring out how to tell it to select a particular row
when I don't know what the row number will be. I've
written the following code:

Dim rng As Range
Dim cell As Range

Set rng = Range(Cells(2, 1), Cells(Rows.Count, 1).End
(xlUp))
For Each cell In rng
If cell.Value = 1 Then
Rows(cell).Select
Selection.Insert Shift:=xlDown

End If
Next

the problem is that because the first cell is A2, it stops
and selects that row and inserts the line at that point,
regardless of whether the cell contains a "1", and doesn't
go on to the next cell in the range.