Removing Subtotals

[paul h]

Here is the trouble.

We recieve files throughout the month that contain subtotals. I need to remove them and create a table to be used elsewhere. Very simple you would think. I recorded a few macros that did the trick and edited them together, tested them several times till I got what I wanted and was very happy till I got the next file where it failed to work. The macro removed the subtotal feature, but left the subtotal lines in the spreadsheet forcing a manual process to filter out and delete those line. A little detective work showed the when clicking on the subtotal icon, the dialog did not reflect the subtotals in the table

Actual Subtotal criteria was sum of Balance by Account Name. When clicking on the dialog it shows Sum of Notes by Invoice Number. When you click remove all it removes is the Subtotal features in the left window, but leaves each subtotal line, which then has to be filtered and deleted. The way around this is to fix the subtotal box, then click remove all, but I can't record this in my macro this way. It just says Cells.RemoveSubtotal.

This must sound confusing. Ask away and I'll try to clarify. If I can get through this subtotals problems, the rest is not too dificult.

Thanks in advance.

[judgeh59]

can you upload a file with confidential info removed?....

[paul h]

This should do it. Run Reformat.

[judgeh59]

If I understand this once you have removed the subtotals you want to deleted all of the rows that were the subtotals and grand totals?....this piece of code will do that....

Please Login or Register  to view this content.

you can change the IF statement to delete whatever you want....this deletes any rows that don't start with "I"

[paul h]

What I want if for the subtotals to be removed by the subtotal tool. I don't understand why these ghosts are remaining behind. I can program around it. It just doesn't seem necessary.

If I start with a flat table and add subtotals, I get something close to what they are sending me. If I reverse the process, it goes back to a flat table. It seems this should do the same thing. I can't figure out why it doesn't. The real solution is to get the vendor to send me the flat file, but until then I though I could just remove subtotals, delete a few rows and move on. Really it just bugs me, but I'll get over it. It's tough being OCD.

Thanks for your contribution.

Paul

[judgeh59]

Sorry. I'm seeing where/why it's not clearing either....I'm not sure I can help....