Insert row when data changes

danwilk

New Member
Joined
Aug 9, 2004
Messages
5
I have an automated process that downloads data from our AS400 into a spreadsheet and then I have to spend time manually adding blank rows and summing columns. So, how would I create a macro to add a new row when the data in column A changes starting at A2. The new row would need to go before the new data in column A. And also I would like to sum two columns when I add this new row.

Example of my data:
Code:
A1   Vendor   Part#        Qty    Cost
A2   00100    part#001    10    1.99
A3   00100    part#002    15    2.49
A4   00200    part#001    10    1.99

I would like to add a blank row before vendor 00200 and sum the qty and the cost.

Thanks for your help it is greatly appreciated. [/code]
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

sweater_vests_rock

Well-known Member
Joined
Oct 8, 2004
Messages
1,657
danwilk.

why don't you just use Excel's subtotals feature? highlight your data, goto Data on the menubar, and select Subtotals...

If an information box pops up, select OK.

At the Subtotals dialog box, the first dropdown list allows you to designate column to watch for change. for you, this would be the Vendor. the second dropdown list allows you to choose a function to use -- pick Sum. In the third box (a scroll-y checkbox), check the box corresponding to "Quantity."

Excel will now automatically create subtotals from the Quantity column anytime that you Vendor column changes. Pretty neat, huh?

Hope this helps. Ben.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,298
Messages
5,595,310
Members
413,986
Latest member
Elizsk

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
Top