Insert Rows

[marc747]

Hi,
I have a Excel file in Column "C" I have numbers like
101,103,108,110,111, and so on..... also in Column "D" I have numbers
like 15002,19404 ...... I need a macro that first looks in Column "C"
and whenever the numbers change to insert 2 rows right where the
change take place. And then Look in Column "D" and do the same.

I appreciate any help Thanks!

 

[JLGWhiz]

The first column is easy:

Sub InsrtRw()
Dim lr As Long
Dim Rng1 As Range
lr = Cells(Rows.Count, 3).End(xlUp).Row
Set Rng1 = Range("C1:C" & lr)
For i = lr To 2 Step -1
If Range("C" & i) <> Range("C" & i - 1) Then
Range("C" & i & ":C" & i + 1).EntireRow.Insert
End If
Next
End Sub

However, once you insert blank rows for column C, it leaves a lot of blank
spaces in column D also. It could be done for column D, but not using the
same logic as for column C.

 

[J Sedoff comRemove>]

For the line:

If Range("C" & i) <> Range("C" & i - 1) Then

Change it to:

If (Range("C" & i) <> Range("C" & i - 1)) or (Range("D" & i) <> Range("D" & i - 1)) Then

That will add your rows if either Column C or D have different values than
their next row. That'll work for you if you don't want four blank rows when
you have different values for BOTH C and D in the next rows.

Hope this helps, Jim

 

[JLGWhiz]

That would still put two blank rows in one column or the other at a point
that is not a value change. But if that does not matter to the OP, then it
is a time saver.

 

[marc747]

Hi,
Thanks, Its works but a few small issue. It will be great if you can
look at the Macro and see if I looks correct.
1)The Columns now are "HB" and "HC"
2)When I run the Macro it inserts Rows below the Header, It should
start inserting from Row2.
3) How would I adjust number of rows to insert.


Sub InsrtRw()
Dim lr As Long
Dim Rng1 As Range
lr = Cells(Rows.Count, 3).End(xlUp).Row
Set Rng1 = Range("HB2:C" & lr)
For i = lr To 2 Step -1
If (Range("HB" & i) <> Range("HB" & i - 1)) Then
Range("HB" & i & ":C" & i + 1).EntireRow.Insert

End If

If (Range("HC" & i) <> Range("HC" & i - 1)) Then
Range("HC" & i & ":C" & i + 1).EntireRow.Insert

End If
Next
End Sub