Excel manual subtotals

[Yann]

Hi All,

Need some help on this little problem....

I've got 4 columns in a spreadsheet (nb of lines will vary as well as the number of types):
Type Prduct ID Qty Value A ABC 1 100 A BVF 2 250 B RTT 1 350 D RWW 2 22 D IIU 5 54
What I need to do, is to insert a blank line above each type (group) and one after each type so I can insert subtotals. Excel built-in subtotals do not allow me to insert blank lines above the group.

Can someone help?

Thanks a lot
Yann

[Bob Phillips]

Off the top



Public Sub ProcessData()
Dim Lastrow As Long
Dim i As Long
Dim cell As Range

Application.ScreenUpdating = False

With ActiveSheet

Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = Lastrow To 2 Step -1

If .Cells(i, "A").Value2 <> .Cells(i - 1, "A").Value2 Then

.Rows(i).Insert
If i <> 2 Then .Rows(i).Insert
End If
Next i
End With

Application.ScreenUpdating = True
End Sub

[Yann]

Hi XLD,

Thanks a lot for your help. I realised that even with the code you provided, my VBA skills are too limited to complete what I need; could you please assist again?

Basically, I need to retrieve on the blank line above the first group (in column B) the type's name (and change the font color) and below the group (in column B) the name of the group above followed by "sub-total" and put it in bold.

Something like:

Type ID Qty
ABC
ABC a1 1
ABC v2 3
ABC Sub-Total 4

etc for the remaining of the list. I tought about using Pivot Table, but the formatted result is not good for what I need.


Thanks again for your help
Yann