Addition of rows in table with loop

  • Thread starter Thread starter desmondleow
  • Start date Start date
D

desmondleow

I have the following code to help me insert a row in between groups o
similar data.
However, it doesn't work after the first row has been inserted. Coul
someone tell me what's wrong with my code?

For Each c In ActiveWorkbook.Worksheets("CIT
GF").Range(myrange.Address)

If Cells(c.Row, 9).Value <> Cells(c.Row + 1, 9).Value Then
'Address = c.Address
c.EntireRow.Offset(1, 0).Select
Selection.Insert Shift:=xlDown
'Range("c.Row,c.column").Offset(1, 0).Select
c = c.Offset(3, 0)
End If
Next c


Regard
 
Hi...

It still doesn't work. It keeps inserting new rows and doesn't move
down to the next line of data. Is there any reason why?

Regards,
Desmond
 
The problem is that when you insert the row you change the are being
processed, and the inserted row becomes part of the next test. This never
matches, so you insert another row at the same point, and so on, until you
get an error.

What you need to do is work from the bottom up, like so

With ActiveWorkbook.Worksheets("CITIGF").Range(myrange)
For i = Cells(Rows.Count, .Column).End(xlUp).Row To .Row + 1 Step -1
If Cells(i, 9).Value <> Cells(i - 1, 9).Value Then
'Address = c.Address
Cells(i, 1).EntireRow.Insert Shift:=xlDown
End If
Next i
End With


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Back
Top