How to insert a row and auto sum by item type

excelzen

New Member
Joined
Jun 24, 2015
Messages
6
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
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
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
ABI
1Item TypeTotals
2100969
31036620
41036629
51036623
61037020
7103705
81037013
91037080
101037010
111037011
121037029
131038520
14
15
16
17
18
Sheet1


After the macro:


Excel 2007
ABI
1Item TypeTotals
2100969
39
41036620
51036629
61036623
772
81037020
9103705
101037013
111037080
121037010
131037011
141037029
15168
161038520
1720
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.

Code:
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.
 
Upvote 0
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
AI
6
7004Q9C1000
8004R1C1000
9004R1C2000
10004R1C2000
11004R1C2000
12004R1C2000
13005G3C2000
14005G3C2000
15008G9C2000
16
17
18
19
20
Sheet1


After the new macro:


Excel 2007
AI
6
7004Q9C1000
81000
9004R1C1000
10004R1C2000
11004R1C2000
12004R1C2000
13004R1C2000
149000
15005G3C2000
16005G3C2000
174000
18008G9C2000
192000
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).

Code:
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.
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,337
Members
448,568
Latest member
Honeymonster123

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