Excel VBA Add subtotals and remove rows

Question

I am trying to figure out how I can add a row with a sum based on the values of a different column. When I do this, I'd like to remove the rows that added up to that value and retain just one row.

For instance, Column A has PO 10404, PO 10404,PO 10404,PO 10404, PO 10405, PO 10405. I'd like to add a row in between PO 10404 and PO 10405 and then sum the dollar amounts of both PO's (I'm Guessing with a SumIf's) and then delete the individual line and just keep the one line with the total. I would need the contents of the common line, like the PO number.

Thanks! Joe

Answer 1

You could do this with a Pivot Table. Use "PO#" as your row label and Sum of "Sale Amount" (or whatever that column is called) for the values.

EDIT: Helpful tips for automating this as macro:

http://www.globaliconnect.com/excel/index.php?option=com_content&view=article&id=147:create-an-excel-pivot-table-report-using-vba&catid=79&Itemid=475

Then to automate saving as a CSV:

ActiveWorkbook.SaveAs Filename:= _
"c:\MyFile.csv", FileFormat:=xlCSV _
, CreateBackup:=False

Answer 2

This isn't really a programming question, but goto

Data>Outline>Subtotal>At Each Change In (Po #)>Use (sum functioN) > Select the dollar amounts column.

Then, collapse the groups, and copy paste values on the subtotals, and delete the grouped rows.