Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert Row Macro based on contents of two columns
Good morning group.
The below is a very useful macro to insert rows where the contents of column B change. How might it be modified so that it inserts rows where the contents of columns A and B change? Sub InsertRow_At_Change() Dim i As Long With Application .Calculation = xlManual .ScreenUpdating = False End With For i = Cells(Rows.Count, 2).End(xlUp).Row To 2 Step -1 If Cells(i - 1, 2) < Cells(i, 2) Then _ Cells(i, 1).Resize(1, 1).EntireRow.Insert Next i With Application .Calculation = xlAutomatic .ScreenUpdating = True End With End Sub Many thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert Row Macro based on contents of two columns
How about simply:
If Cells(i - 1, 2) < Cells(i, 2) And Cells(i - 1, 1) < Cells(i, 1) Then _ Cells(i, 1).Resize(1, 1).EntireRow.Insert RBS "5elpep" wrote in message ups.com... Good morning group. The below is a very useful macro to insert rows where the contents of column B change. How might it be modified so that it inserts rows where the contents of columns A and B change? Sub InsertRow_At_Change() Dim i As Long With Application .Calculation = xlManual .ScreenUpdating = False End With For i = Cells(Rows.Count, 2).End(xlUp).Row To 2 Step -1 If Cells(i - 1, 2) < Cells(i, 2) Then _ Cells(i, 1).Resize(1, 1).EntireRow.Insert Next i With Application .Calculation = xlAutomatic .ScreenUpdating = True End With End Sub Many thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert Row Macro based on contents of two columns
Thanks for your input RB.
Unfortunately I mis-typed my question. I meant to say how might it be modified so that it inserts rows where the contents of columns A OR B change? Also I then need to change the blank row so that column C displays "CAR", column D returns =OFFSET(D*,1,-3) and column E returns =OFFSET(E*,1,-3). To use a silly example: MON 12 XKR MON 12 FOCUS MON 12 LAND CRUISER TUES 13 HUMMER Will look as below on running the code MON 12 XKR MON 12 FOCUS MON 12 LAND CRUISER CAR TUES 13 TUES 13 HUMMER and so on |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert Row Macro based on contents of two columns
Then change the And to an Or and put some extra lines of code in to get your
text "CAR" and the offsets. RBS "5elpep" wrote in message oups.com... Thanks for your input RB. Unfortunately I mis-typed my question. I meant to say how might it be modified so that it inserts rows where the contents of columns A OR B change? Also I then need to change the blank row so that column C displays "CAR", column D returns =OFFSET(D*,1,-3) and column E returns =OFFSET(E*,1,-3). To use a silly example: MON 12 XKR MON 12 FOCUS MON 12 LAND CRUISER TUES 13 HUMMER Will look as below on running the code MON 12 XKR MON 12 FOCUS MON 12 LAND CRUISER CAR TUES 13 TUES 13 HUMMER and so on |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert Row Macro based on contents of two columns
Is it possible to modified this macro so the new row, column C, contains
the sum of that group's column C values? Thank you much! 5elpep wrote: Good morning group. The below is a very useful macro to insert rows where the contents of column B change. How might it be modified so that it inserts rows where the contents of columns A and B change? Sub InsertRow_At_Change() Dim i As Long With Application .Calculation = xlManual .ScreenUpdating = False End With For i = Cells(Rows.Count, 2).End(xlUp).Row To 2 Step -1 If Cells(i - 1, 2) < Cells(i, 2) Then _ Cells(i, 1).Resize(1, 1).EntireRow.Insert Next i With Application .Calculation = xlAutomatic .ScreenUpdating = True End With End Sub Many thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hiding rows or columns based on cell contents | Excel Discussion (Misc queries) | |||
macro with input msg based on cell contents | Excel Discussion (Misc queries) | |||
Excel Macro to Insert the contents of one column to other | Excel Discussion (Misc queries) | |||
Insert Columns based on cell value | Excel Programming | |||
Macro to Sort Automatically Based on Contents of Drop-down Box | Excel Programming |