How to insert a row and auto sum by item type

[excelzen]

How can I automatically insert a row after each item type in column A to auto sum their totals in column I?

10096 9
10366 20
10366 29
10366 23
10370 20
10370 5
10370 13
10370 80
10370 10
10370 11
10370 29
10385 20

 

[hiker95]

excelzen,

1. What version of Excel, and, Windows are you using?

2. Are you using a PC or a Mac?

Here is a macro solution for you to consider, based on the flat text that you have displayed, where the Item Type's are grouped/sorted.

You can change the raw data worksheet name in the macro.

Sample raw data:

Excel 2007
	A	B	I
1	Item Type		Totals
2	10096	9
3	10366	20
4	10366	29
5	10366	23
6	10370	20
7	10370	5
8	10370	13
9	10370	80
10	10370	10
11	10370	11
12	10370	29
13	10385	20
14
15
16
17
18
Sheet1

After the macro:

Excel 2007
	A	B	I
1	Item Type		Totals
2	10096	9
3			9
4	10366	20
5	10366	29
6	10366	23
7			72
8	10370	20
9	10370	5
10	10370	13
11	10370	80
12	10370	10
13	10370	11
14	10370	29
15			168
16	10385	20
17			20
18
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sub AutoSumItemType()
' hiker95, 07/08/2015, ME866881
Dim w1 As Worksheet
Dim r As Long, lr As Long
Dim Area As Range, sr As Long, er As Long
Application.ScreenUpdating = False
Set w1 = Sheets("Sheet1")   '<-- you can change the sheet name here
With w1
  lr = .Cells(Rows.Count, 1).End(xlUp).Row
  For r = lr To 3 Step -1
    If .Cells(r, 1) <> .Cells(r - 1, 1) Then
      .Rows(r).Insert
    End If
  Next r
  lr = .Cells(Rows.Count, 1).End(xlUp).Row
  For Each Area In .Range("A2:A" & lr).SpecialCells(xlCellTypeConstants).Areas
    With Area
      sr = .Row
      er = sr + .Rows.Count - 1
      With w1.Range("I" & er + 1)
        .Value = Evaluate("=Sum(B" & sr & ":B" & er & ")")
        .Font.Bold = True
      End With
    End With
  Next Area
End With
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the AutoSumItemType macro.

 

[excelzen]

Hi Hiker95,

Your code sorted perfectly, but it did not auto sum the totals? I tried this on a mac version 2011, and pc version 2010. I'm not sure how to paste an example of the file in here, but I uploaded the sheet to google for you.

https://docs.google.com/spreadsheets/d/1KqS8zuZnXq5CBShbJYQwfqmDWuDQyGeesxcfe4QEP8c/edit?usp=sharing

 

[hiker95]

excelzen,

Thanks for the workbook.

1. Your raw data Item Type's actually start in row 7.

2. The numbers to sum are actually in column I, beginning in row 7.

Here is another macro for you to consider, if the following screenshots are correct.

You can change the raw data worksheet name in the macro.

Sample raw data:

Excel 2007
	A	I
6
7	004Q9C	1000
8	004R1C	1000
9	004R1C	2000
10	004R1C	2000
11	004R1C	2000
12	004R1C	2000
13	005G3C	2000
14	005G3C	2000
15	008G9C	2000
16
17
18
19
20
Sheet1

After the new macro:

Excel 2007
	A	I
6
7	004Q9C	1000
8		1000
9	004R1C	1000
10	004R1C	2000
11	004R1C	2000
12	004R1C	2000
13	004R1C	2000
14		9000
15	005G3C	2000
16	005G3C	2000
17		4000
18	008G9C	2000
19		2000
20
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Sub AutoSumItemType_V2()
' hiker95, 07/09/2015, ME866881
Dim w1 As Worksheet
Dim r As Long, lr As Long
Dim Area As Range, sr As Long, er As Long
Application.ScreenUpdating = False
Set w1 = Sheets("Sheet1")   '<-- you can change the sheet name here
With w1
  lr = .Cells(Rows.Count, 1).End(xlUp).Row
  For r = lr To 8 Step -1
    If .Cells(r, 1) <> .Cells(r - 1, 1) Then
      .Rows(r).Insert
    End If
  Next r
  lr = .Cells(Rows.Count, 1).End(xlUp).Row
  For Each Area In .Range("A7:A" & lr).SpecialCells(xlCellTypeConstants).Areas
    With Area
      sr = .Row
      er = sr + .Rows.Count - 1
      With w1.Range("I" & er + 1)
        .Value = Evaluate("=Sum(I" & sr & ":I" & er & ")")
        .Font.Bold = True
      End With
    End With
  Next Area
End With
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the AutoSumItemType_V2 macro.