VBA Code to Loop and Calculate Subtotals with blank rows

[Steve Belsch]

VBA Experts,

I could use some help with some code. I have attached an workbook with the code that I have built so far. What the code does is calculates subtotals on the each unique PO# (column A). Then it adds 5 blank rows.

Here is my issue:

I want the Subtotal formula to include all the rows for that specific PO and the blank rows. For example in the tab named "05.301", PO# 237932 the Code will insert a row in row 20. Then it will calculate the subtotal and then insert 5 blank rows. But the formula looks like this =Subtotal(9,M$3:M19) and I want it to include the additional blank rows so it should be =Subtotal(9,M$3:M24). And I don't want the the last cell (M24 in this example) in the equation locked, no $.

Any ideas?

Thanks.
Steve

[Paul_Hossler]

I think you're making the a lot harder than need be

I don't understand the '5 blank lines' or a lot of the other stuff. and I deleted all but one worksheet since it takes way to long to calculate

To ask a question, it's really only necessary to provide a small sample workbook that shows the specific problem/question, not one that takes 10 minutes to open and recalculate



The easiest way to add Subtotal() formulas is to use Excel's capability:




Option Explicit


Sub phh()
Worksheets("Accrual & PO Data").Cells(1, 1).CurrentRegion.SubTotal _
GroupBy:=1, Function:=xlSum, _
TotalList:=Array(13, 14, 15, 25, 26, 28), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True

End Sub






Personally, I'd change my formatting requirements to stay with the 'Excel-way' of concepts instead of adding blank lines, etc.



Sub phh2()
With Worksheets("Accrual & PO Data")
With .Cells(1, 1).CurrentRegion
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlTop
End With

'Subtotal row, hardcoded in example, but easily changed
.Rows("3:3").RowHeight = 3 * .StandardHeight


End With
End Sub

[Steve Belsch]

Paul,

Thanks for the help. I am sorry if I made my message confusing. I will simplify it here. The macro works fine. The only thing that I can't figure out how to do is add 5 blank rows above the subtotal line that are included in the subtotal calculation.

For example if you want to insert a row without using a macro it automatically adjusts the formula. Example:

Before inserting row - M$3:M10
After inserting row - M$3:M11

But in my macro it does not adjust the subtotal to capture these extra rows. Does that make sense?

Any ideas on how to fix this with VBA code?

Thanks.
Steve