VBA - Insert blank row and sum values in column C for each value in column D

J

jmk1153

New Member
Joined
Jun 27, 2018
Messages
14
Hello,

I think what I'm trying to accomplish is relatively easy but I am really struggling on this one...

One of the problems is I will need to do this everyday with different data so I'm trying to use a formula that can be used for all data in this same format.

I have a sheet with 4 columns. What I'm trying to do is, insert two new blank rows after each change in value in column D (e.g. two new rows between A36 and A37, as well as A81 and A82). The first new row would contains the sum of values in column C for that group.

So a new row would be inserted as 37 and 38. Cell C37 would contain the value 18,041. Row 38 would remain blank. The new row inserted for row 68 would contain a value of -10,490 in C68, etc. Row 69 would remain blank.

Same deal for the Hyundia group.

Is this way more complicated than I think?

[TABLE="width: 555"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Person[/TD]
[TD]Car[/TD]
[TD]Sales[/TD]
[TD]Dealership[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Accord[/TD]
[TD]4500[/TD]
[TD]Honda[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Accord[/TD]
[TD]4600[/TD]
[TD]Honda[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Accord[/TD]
[TD]4600[/TD]
[TD]Honda[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Accord[/TD]
[TD]2500[/TD]
[TD]Honda[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Accord[/TD]
[TD]520[/TD]
[TD]Honda[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Accord[/TD]
[TD]178[/TD]
[TD]Honda[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Accord[/TD]
[TD]154[/TD]
[TD]Honda[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Accord[/TD]
[TD]148[/TD]
[TD]Honda[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Accord[/TD]
[TD]115[/TD]
[TD]Honda[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Accord[/TD]
[TD]97[/TD]
[TD]Honda[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Accord[/TD]
[TD]67[/TD]
[TD]Honda[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Accord[/TD]
[TD]63[/TD]
[TD]Honda[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Accord[/TD]
[TD]60[/TD]
[TD]Honda[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Accord[/TD]
[TD]51[/TD]
[TD]Honda[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Accord[/TD]
[TD]40[/TD]
[TD]Honda[/TD]
[/TR]
[TR]
[TD]Sheila[/TD]
[TD]S2000[/TD]
[TD]38[/TD]
[TD]Honda[/TD]
[/TR]
[TR]
[TD]Sheila[/TD]
[TD]S2000[/TD]
[TD]35[/TD]
[TD]Honda[/TD]
[/TR]
[TR]
[TD]Sheila[/TD]
[TD]S2000[/TD]
[TD]34[/TD]
[TD]Honda[/TD]
[/TR]
[TR]
[TD]Sheila[/TD]
[TD]S2000[/TD]
[TD]26[/TD]
[TD]Honda[/TD]
[/TR]
[TR]
[TD]Sheila[/TD]
[TD]S2000[/TD]
[TD]25[/TD]
[TD]Honda[/TD]
[/TR]
[TR]
[TD]Sheila[/TD]
[TD]S2000[/TD]
[TD]25[/TD]
[TD]Honda[/TD]
[/TR]
[TR]
[TD]Sheila[/TD]
[TD]S2000[/TD]
[TD]24[/TD]
[TD]Honda[/TD]
[/TR]
[TR]
[TD]Sheila[/TD]
[TD]S2000[/TD]
[TD]23[/TD]
[TD]Honda[/TD]
[/TR]
[TR]
[TD]Sheila[/TD]
[TD]S2000[/TD]
[TD]21[/TD]
[TD]Honda[/TD]
[/TR]
[TR]
[TD]Sheila[/TD]
[TD]S2000[/TD]
[TD]19[/TD]
[TD]Honda[/TD]
[/TR]
[TR]
[TD]Sheila[/TD]
[TD]S2000[/TD]
[TD]16[/TD]
[TD]Honda[/TD]
[/TR]
[TR]
[TD]Sheila[/TD]
[TD]S2000[/TD]
[TD]15[/TD]
[TD]Honda[/TD]
[/TR]
[TR]
[TD]Sheila[/TD]
[TD]S2000[/TD]
[TD]11[/TD]
[TD]Honda[/TD]
[/TR]
[TR]
[TD]Sheila[/TD]
[TD]S2000[/TD]
[TD]7[/TD]
[TD]Honda[/TD]
[/TR]
[TR]
[TD]Sheila[/TD]
[TD]S2000[/TD]
[TD]7[/TD]
[TD]Honda[/TD]
[/TR]
[TR]
[TD]Sheila[/TD]
[TD]S2000[/TD]
[TD]7[/TD]
[TD]Honda[/TD]
[/TR]
[TR]
[TD]Sheila[/TD]
[TD]S2000[/TD]
[TD]6[/TD]
[TD]Honda[/TD]
[/TR]
[TR]
[TD]Sheila[/TD]
[TD]S2000[/TD]
[TD]3[/TD]
[TD]Honda[/TD]
[/TR]
[TR]
[TD]Sheila[/TD]
[TD]S2000[/TD]
[TD]3[/TD]
[TD]Honda[/TD]
[/TR]
[TR]
[TD]Sheila[/TD]
[TD]S2000[/TD]
[TD]3[/TD]
[TD]Honda[/TD]
[/TR]
[TR]
[TD]Edward[/TD]
[TD]Outback[/TD]
[TD]3[/TD]
[TD]Subaru[/TD]
[/TR]
[TR]
[TD]Edward[/TD]
[TD]Outback[/TD]
[TD]2[/TD]
[TD]Subaru[/TD]
[/TR]
[TR]
[TD]Edward[/TD]
[TD]Outback[/TD]
[TD]2[/TD]
[TD]Subaru[/TD]
[/TR]
[TR]
[TD]Edward[/TD]
[TD]Outback[/TD]
[TD]2[/TD]
[TD]Subaru[/TD]
[/TR]
[TR]
[TD]Edward[/TD]
[TD]Outback[/TD]
[TD]1[/TD]
[TD]Subaru[/TD]
[/TR]
[TR]
[TD]Edward[/TD]
[TD]Outback[/TD]
[TD]1[/TD]
[TD]Subaru[/TD]
[/TR]
[TR]
[TD]Edward[/TD]
[TD]Outback[/TD]
[TD]1[/TD]
[TD]Subaru[/TD]
[/TR]
[TR]
[TD]Edward[/TD]
[TD]Outback[/TD]
[TD]1[/TD]
[TD]Subaru[/TD]
[/TR]
[TR]
[TD]Edward[/TD]
[TD]Outback[/TD]
[TD]-1524[/TD]
[TD]Subaru[/TD]
[/TR]
[TR]
[TD]Edward[/TD]
[TD]Outback[/TD]
[TD]-1083[/TD]
[TD]Subaru[/TD]
[/TR]
[TR]
[TD]Edward[/TD]
[TD]Outback[/TD]
[TD]-834[/TD]
[TD]Subaru[/TD]
[/TR]
[TR]
[TD]Edward[/TD]
[TD]Outback[/TD]
[TD]-600[/TD]
[TD]Subaru[/TD]
[/TR]
[TR]
[TD]Edward[/TD]
[TD]Outback[/TD]
[TD]-208[/TD]
[TD]Subaru[/TD]
[/TR]
[TR]
[TD]Edward[/TD]
[TD]Outback[/TD]
[TD]-94[/TD]
[TD]Subaru[/TD]
[/TR]
[TR]
[TD]Edward[/TD]
[TD]Outback[/TD]
[TD]-89[/TD]
[TD]Subaru[/TD]
[/TR]
[TR]
[TD]Edward[/TD]
[TD]Outback[/TD]
[TD]-83[/TD]
[TD]Subaru[/TD]
[/TR]
[TR]
[TD]Edward[/TD]
[TD]Impreza[/TD]
[TD]-82[/TD]
[TD]Subaru[/TD]
[/TR]
[TR]
[TD]Edward[/TD]
[TD]Impreza[/TD]
[TD]-81[/TD]
[TD]Subaru[/TD]
[/TR]
[TR]
[TD]Edward[/TD]
[TD]Impreza[/TD]
[TD]-1524[/TD]
[TD]Subaru[/TD]
[/TR]
[TR]
[TD]Edward[/TD]
[TD]Impreza[/TD]
[TD]-1083[/TD]
[TD]Subaru[/TD]
[/TR]
[TR]
[TD]Edward[/TD]
[TD]Impreza[/TD]
[TD]-834[/TD]
[TD]Subaru[/TD]
[/TR]
[TR]
[TD]Edward[/TD]
[TD]Impreza[/TD]
[TD]-600[/TD]
[TD]Subaru[/TD]
[/TR]
[TR]
[TD]Edward[/TD]
[TD]Impreza[/TD]
[TD]-208[/TD]
[TD]Subaru[/TD]
[/TR]
[TR]
[TD]Edward[/TD]
[TD]Impreza[/TD]
[TD]-94[/TD]
[TD]Subaru[/TD]
[/TR]
[TR]
[TD]Edward[/TD]
[TD]Impreza[/TD]
[TD]-89[/TD]
[TD]Subaru[/TD]
[/TR]
[TR]
[TD]Edward[/TD]
[TD]Impreza[/TD]
[TD]-83[/TD]
[TD]Subaru[/TD]
[/TR]
[TR]
[TD]Edward[/TD]
[TD]Impreza[/TD]
[TD]-82[/TD]
[TD]Subaru[/TD]
[/TR]
[TR]
[TD]Edward[/TD]
[TD]Impreza[/TD]
[TD]-81[/TD]
[TD]Subaru[/TD]
[/TR]
[TR]
[TD]Edward[/TD]
[TD]Impreza[/TD]
[TD]-208[/TD]
[TD]Subaru[/TD]
[/TR]
[TR]
[TD]Edward[/TD]
[TD]Impreza[/TD]
[TD]-94[/TD]
[TD]Subaru[/TD]
[/TR]
[TR]
[TD]Edward[/TD]
[TD]Impreza[/TD]
[TD]-89[/TD]
[TD]Subaru[/TD]
[/TR]
[TR]
[TD]Jeff[/TD]
[TD]Legacy[/TD]
[TD]-83[/TD]
[TD]Subaru[/TD]
[/TR]
[TR]
[TD]Jeff[/TD]
[TD]Legacy[/TD]
[TD]-82[/TD]
[TD]Subaru[/TD]
[/TR]
[TR]
[TD]Jeff[/TD]
[TD]Legacy[/TD]
[TD]-81[/TD]
[TD]Subaru[/TD]
[/TR]
[TR]
[TD]Jeff[/TD]
[TD]Legacy[/TD]
[TD]-80[/TD]
[TD]Subaru[/TD]
[/TR]
[TR]
[TD]Jeff[/TD]
[TD]Legacy[/TD]
[TD]-80[/TD]
[TD]Subaru[/TD]
[/TR]
[TR]
[TD]Jeff[/TD]
[TD]Legacy[/TD]
[TD]-73[/TD]
[TD]Subaru[/TD]
[/TR]
[TR]
[TD]Jeff[/TD]
[TD]Legacy[/TD]
[TD]-59[/TD]
[TD]Subaru[/TD]
[/TR]
[TR]
[TD]Jeff[/TD]
[TD]Legacy[/TD]
[TD]-47[/TD]
[TD]Subaru[/TD]
[/TR]
[TR]
[TD]Jeff[/TD]
[TD]Legacy[/TD]
[TD]-37[/TD]
[TD]Subaru[/TD]
[/TR]
[TR]
[TD]Jeff[/TD]
[TD]Legacy[/TD]
[TD]-30[/TD]
[TD]Subaru[/TD]
[/TR]
[TR]
[TD]Jeff[/TD]
[TD]Legacy[/TD]
[TD]-28[/TD]
[TD]Subaru[/TD]
[/TR]
[TR]
[TD]Jeff[/TD]
[TD]Legacy[/TD]
[TD]-23[/TD]
[TD]Subaru[/TD]
[/TR]
[TR]
[TD]Jeff[/TD]
[TD]Legacy[/TD]
[TD]-22[/TD]
[TD]Subaru[/TD]
[/TR]
[TR]
[TD]Jeff[/TD]
[TD]Legacy[/TD]
[TD]-18[/TD]
[TD]Subaru[/TD]
[/TR]
[TR]
[TD]Svea[/TD]
[TD]Santa Fe[/TD]
[TD]4500[/TD]
[TD]Hyundai[/TD]
[/TR]
[TR]
[TD]Svea[/TD]
[TD]Santa Fe[/TD]
[TD]3200[/TD]
[TD]Hyundai[/TD]
[/TR]
[TR]
[TD]Svea[/TD]
[TD]Santa Fe[/TD]
[TD]4600[/TD]
[TD]Hyundai[/TD]
[/TR]
[TR]
[TD]Svea[/TD]
[TD]Santa Fe[/TD]
[TD]11[/TD]
[TD]Hyundai[/TD]
[/TR]
[TR]
[TD]Svea[/TD]
[TD]Santa Fe[/TD]
[TD]9[/TD]
[TD]Hyundai[/TD]
[/TR]
[TR]
[TD]Svea[/TD]
[TD]Santa Fe[/TD]
[TD]9[/TD]
[TD]Hyundai[/TD]
[/TR]
[TR]
[TD]Svea[/TD]
[TD]Santa Fe[/TD]
[TD]8[/TD]
[TD]Hyundai[/TD]
[/TR]
[TR]
[TD]Svea[/TD]
[TD]Santa Fe[/TD]
[TD]7[/TD]
[TD]Hyundai[/TD]
[/TR]
[TR]
[TD]Svea[/TD]
[TD]Santa Fe[/TD]
[TD]7[/TD]
[TD]Hyundai[/TD]
[/TR]
[TR]
[TD]Svea[/TD]
[TD]Santa Fe[/TD]
[TD]7[/TD]
[TD]Hyundai[/TD]
[/TR]
[TR]
[TD]Svea[/TD]
[TD]Santa Fe[/TD]
[TD]6[/TD]
[TD]Hyundai[/TD]
[/TR]
[TR]
[TD]Svea[/TD]
[TD]Santa Fe[/TD]
[TD]5[/TD]
[TD]Hyundai[/TD]
[/TR]
[TR]
[TD]Svea[/TD]
[TD]Santa Fe[/TD]
[TD]5[/TD]
[TD]Hyundai[/TD]
[/TR]
[TR]
[TD]Svea[/TD]
[TD]Santa Fe[/TD]
[TD]5[/TD]
[TD]Hyundai[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Sort by date Sort by votes
Try this

Code: 
Sub Insert_blank_row()
    Dim i As Long, b As Range
    For i = Range("D" & Rows.Count).End(xlUp).Row To 2 Step -1
        dealer = Cells(i, "D").Value
        Set b = Range("D:D").Find(dealer, LookIn:=xlValues, lookat:=xlWhole)
        If Not b Is Nothing Then
            Rows(i + 1 & ":" & i + 2).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
            Cells(i + 1, "C").Value = WorksheetFunction.Sum(Range("C" & b.Row, "C" & i))
            i = b.Row
        End If
    Next
End Sub

You can also put subtotals with a Pivot table or with the subtotals excel functionality. eg.

Subtotals---Figure-1.png
 
Last edited:
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,223,909
Messages
6,175,313
Members
452,634
Latest member
cpostell

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top