Insert Rows
 

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!

Insert Rows
 

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.

" wrote:

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!

Insert Rows
 

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
--
I appreciate any feedback. Please don''t be scared to say that "Yes"
I/someone else did answer your question. Thank you.


"JLGWhiz" wrote:

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

Insert Rows
 

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.

"J Sedoff" wrote:

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
--
I appreciate any feedback. Please don''t be scared to say that "Yes"
I/someone else did answer your question. Thank you.


"JLGWhiz" wrote:

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

Insert Rows
 

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

--------------------------------------------------------------------------



On Aug 6, 4:30*pm, JLGWhiz wrote:
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.



"J Sedoff" wrote:
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
--
I appreciate any feedback. *Please don''t be scared to say that "Yes"
I/someone else did answer your question. *Thank you.

"JLGWhiz" wrote:

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- Hide quoted text -

- Show quoted text -