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]
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,087
Messages
5,835,302
Members
430,351
Latest member
ddalton

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