Addition of rows in table with loop

[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

 

[Chip Pearson]

Desmond,

Change

c = c.Offset(3, 0)

To

Set c = c.Offset(3, 0)


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[desmondleow]

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

 

[Bob Phillips]

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)