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

[jmk1153]

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?

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

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

 

[DanteAmor]

Try this

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.

 

[jmk1153]

Thank you!

That works well. That is much simpler than what I was trying. Appreciate the help.

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.