Subtotal on mulitple columns with VBA

Hi Excel Gurus!!! I have a problem where I want to apply a subtotal row and then sum or count various columns when there's a change in the Department Name, in my attached example file column C. In the attached file are two sheets, one is the original file and the other is my desired results.

The desired results shows the subtotal line hightighted with a color to make it stand out. In the example file that goes up to column T. In the case of future changes to this file (for example I add a column based on a users request), I'd like the VBA code to 'know' to now go out to column U, the same if I were to delete a column that the coloring go upto the last cell where there's data.

Any suggested VBA code would get great!!! Thanks all!

Have you looked at using the built-in Subtotal feature in the Data tab?