Insert Row when cell value changes Macro

[logan.m.moore]

I am trying to find a macro to insert a row below when the value of a cell in the selected column changes. Ex:

<TABLE style="WIDTH: 74pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=98 border=0 x:str><COLGROUP><COL style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 3584" width=98><TBODY><TR style="HEIGHT: 13.5pt; mso-height-source: userset" height=18><TD class=xl22 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; WIDTH: 74pt; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" width=98 height=18>JE027539</TD></TR><TR style="HEIGHT: 13.5pt; mso-height-source: userset" height=18><TD class=xl22 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; WIDTH: 74pt; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" width=98 height=18>JE027539</TD></TR><TR style="HEIGHT: 13.5pt; mso-height-source: userset" height=18><TD class=xl22 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; WIDTH: 74pt; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" width=98 height=18>JE027539</TD></TR><TR style="HEIGHT: 13.5pt; mso-height-source: userset" height=18><TD class=xl22 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; WIDTH: 74pt; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" width=98 height=18>JE027539</TD></TR><TR style="HEIGHT: 13.5pt; mso-height-source: userset" height=18><TD class=xl22 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; WIDTH: 74pt; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" width=98 height=18>JE027082</TD></TR></TBODY></TABLE>
The macro I need would detect that cell value changes from JE027539 to JE027082 and would insert a row after the last JE027539. This would need to be done for hundreds of rows with different values throughout. Thanks for your help!!!

 

[jbeaucaire]

Try this:

Sub InsertBlankRows()
'JBeaucaire  (12/10/2009)
Dim lastrow As Long, i As Long
Application.ScreenUpdating = False
lastrow = Range("A" & Rows.Count).End(xlUp).Row

    For i = lastrow To 2 Step -1
        If Cells(i, "A") <> Cells(i - 1, "A") Then Rows(i).Insert xlShiftDown
    Next i

Application.ScreenUpdating = True
End Sub

 

[logan.m.moore]

That worked perfectly. Thank you again for your help!!!

 

[logan.m.moore]

Is it possible to make the macro in the above post, and after inserting the rows, take the last value before inserted row (i.e. JE027106) and add "Total" to the end (i.e. JE027106 Total) and bold it in the blank cell below it? Then after that in column 3, with the dollar values, do formula =subtotal(9,range) for all values above up to next blank space and bold as well. So in the example below bolded formula would go below cell with value 96.00 and range would be equal to 245.75 through 96.00.

These two things would need to be done for hundreds of ranges with varying numbers of cells in ranges and ideally would be added to the already existing macro (posted in reply above) and would all be done as one macro.

If this does not make sense please let me know and I will try to clarify as I know this is a lengthy request. Thank you for your help, it is greatly appreciated!!!

<TABLE style="WIDTH: 488pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=649 border=0 x:str><COLGROUP><COL style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 3584" width=98><COL style="WIDTH: 340pt; mso-width-source: userset; mso-width-alt: 16566" width=453><COL style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 3584" width=98><TBODY><TR style="HEIGHT: 13.5pt; mso-height-source: userset" height=18><TD class=xl22 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; WIDTH: 74pt; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" width=98 height=18>JE027106</TD><TD class=xl22 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver 0.5pt solid; BORDER-LEFT: silver; WIDTH: 340pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width=453>043999911000999999900000104001000000000000000000000000000000</TD><TD class=xl23 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver 0.5pt solid; BORDER-LEFT: silver; WIDTH: 74pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width=98 x:num="75.5">75.50 </TD></TR><TR style="HEIGHT: 13.5pt; mso-height-source: userset" height=18><TD class=xl22 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; WIDTH: 74pt; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" width=98 height=18>JE027106</TD><TD class=xl22 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; WIDTH: 340pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width=453>044999911000999999900000104001000000000000000000000000000000</TD><TD class=xl23 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; WIDTH: 74pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width=98 x:num="125">125.00 </TD></TR><TR style="HEIGHT: 13.5pt; mso-height-source: userset" height=18><TD class=xl22 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; WIDTH: 74pt; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" width=98 height=18></TD><TD class=xl22 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; WIDTH: 340pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width=453></TD><TD class=xl23 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; WIDTH: 74pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width=98></TD></TR><TR style="HEIGHT: 13.5pt; mso-height-source: userset" height=18><TD class=xl22 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; WIDTH: 74pt; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" width=98 height=18>JE027107</TD><TD class=xl22 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; WIDTH: 340pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width=453>001999911000999999900000104001000000000000000000000000000000</TD><TD class=xl23 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; WIDTH: 74pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width=98 x:num="245.75">245.75 </TD></TR><TR style="HEIGHT: 13.5pt; mso-height-source: userset" height=18><TD class=xl22 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; WIDTH: 74pt; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" width=98 height=18>JE027107</TD><TD class=xl22 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; WIDTH: 340pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width=453>041999911000999999900000104001000000000000000000000000000000</TD><TD class=xl23 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; WIDTH: 74pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width=98 x:num="40">40.00 </TD></TR><TR style="HEIGHT: 13.5pt; mso-height-source: userset" height=18><TD class=xl22 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; WIDTH: 74pt; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" width=98 height=18>JE027107</TD><TD class=xl22 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; WIDTH: 340pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width=453>042999911000999999900000104001000000000000000000000000000000</TD><TD class=xl23 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; WIDTH: 74pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width=98 x:num="6.25">6.25 </TD></TR><TR style="HEIGHT: 13.5pt; mso-height-source: userset" height=18><TD class=xl22 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; WIDTH: 74pt; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" width=98 height=18>JE027107</TD><TD class=xl22 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; WIDTH: 340pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width=453>043999911000999999900000104001000000000000000000000000000000</TD><TD class=xl23 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; WIDTH: 74pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width=98 x:num="27">27.00 </TD></TR><TR style="HEIGHT: 13.5pt; mso-height-source: userset" height=18><TD class=xl22 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; WIDTH: 74pt; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" width=98 height=18>JE027107</TD><TD class=xl22 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; WIDTH: 340pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width=453>044999911000999999900000104001000000000000000000000000000000</TD><TD class=xl23 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; WIDTH: 74pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width=98 x:num="96">96.00 </TD></TR><TR style="HEIGHT: 13.5pt; mso-height-source: userset" height=18><TD class=xl22 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; WIDTH: 74pt; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" width=98 height=18></TD><TD class=xl22 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; WIDTH: 340pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width=453></TD><TD class=xl23 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; WIDTH: 74pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width=98></TD></TR><TR style="HEIGHT: 13.5pt; mso-height-source: userset" height=18><TD class=xl22 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; WIDTH: 74pt; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" width=98 height=18>JE027112</TD><TD class=xl22 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; WIDTH: 340pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width=453>001999911000999999900000104001000000000000000000000000000000</TD><TD class=xl23 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; WIDTH: 74pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width=98 x:num="6060.5">6,060.50 </TD></TR><TR style="HEIGHT: 13.5pt; mso-height-source: userset" height=18><TD class=xl22 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; WIDTH: 74pt; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" width=98 height=18>JE027112</TD><TD class=xl22 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; WIDTH: 340pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width=453>042999911000999999900000104001000000000000000000000000000000</TD><TD class=xl23 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; WIDTH: 74pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width=98 x:num="302.5">302.50 </TD></TR></TBODY></TABLE>

 

[Norie]

Why not try Data>Subtotals...?

That should do what you appear to want without any code.

 

[logan.m.moore]

I was unfamiliar with this function. Thank you for your help!