ExcelBanter - Inserting Entire Rows

When you insert or delete rows it is best to
start from the bottom using STEP -1 so that you
don't trip all over yourself with changing rows.

Modified without testing from example
Insert Blank Rows on Column A change of value (#ColAchg)
http://www.mvps.org/dmcritchie/excel...ow.htm#colAchg

Sub InsertRow_C_Chg()
Dim irow As Long, vcurrent As String, i As Long
'// find last used cell in Column A
irow = Cells(Rows.Count, "C").End(xlUp).Row
'// get value of that cell in Column C (column 3)
vcurrent = Cells(irow, 3).Value ' Col C
'// rows are inserted by looping from bottom
For i = irow To 2 Step -1
If Cells(i, 3).Value < vcurrent Then 'Col C
vcurrent = Cells(i, 3).Value 'Col C
Rows(i + 1).Insert
End If
Next i
End Sub
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"TP" wrote in message om...
Hi,

Would somebody be able to help me?
I have a excel spreadsheet and wanted a macro that would insert an
entire row everytime the number above it in column C was different.
eg.

A B C
1 10 1 20
2 11 3 20
3 12 21 30 < Insert Row
4 13 27 30
5 14 11 40 < Insert Row

So far this is the only code i can write but it wont work:

Dim i As Integer
i = 2
While Range("C" & i) < ""
Do
If Range("C" & i) < Range("C" & i + 1) Then
Rows(i & ":" & i + 1).Insert Shift:=xlDown
i = i + 1
End If
i = i + 1
Loop
Wend

End Sub

Would anybody be able to assist me?

TP