Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA to count rows from specific cell and insert rows | Excel Programming | |||
Insert rows: Formats & formulas extended to additonal rows | Excel Worksheet Functions | |||
Insert page breaks every 50 rows but do not include hidden rows | Excel Programming | |||
How do i insert of spacer rows between rows in large spreadsheets | Excel Discussion (Misc queries) | |||
Copy Rows and insert these rows before a page break | Excel Programming |