VBA Code to Total Sections with Varying Number of Rows

[Sawyer84]

I would REALLY like some help with creating a code for section totals and then another code that adds a ?total? row. I just started learning about Macros and VBA codes last week, so this is all pretty new and a little overwhelming to me. I apologize in advance for the long description, it?s hard to explain verbally, let alone capture in words.

I've attached a sample spreadsheet. I've also attached a snapshot, in case you don't want to open the file.

For the section totals, those formulas need to go in columns G/I/K/N in the highlighted and bold rows. The problem is, there could be 1 section, or up to 5 or 6 sections, each having a different number of rows. For example, I would like the section total formula in cells G3/I3/K3/N3 to total up the rows below (items 20/30/40), but stop at the last part number before the next section. I would like to do the same for all sections and option sections, rental sections don?t have to have totals.
There will always be at least 1 section and 1 option section in each quote.
I added ?higher level item? data in column Q. I don?t currently have that information pulling in, but I can if it will make this easier. The ?higher level item? will identify which section parts belong to in a quote. For example, Section A in Quote 1 is Item 10, so every part in Section A will have a higher level item of 10. As you can see Quote 4 also has a Section A that is item 10. Part of the VBA code I currently have will insert 2 rows between quote numbers that are different, so hopefully that helps too.

For the total row, I would like to insert 2 rows above the first option section in each quote. In the first row that was inserted is where I would like ?total? in column D with right aligned. I would like the total formulas in columns G/I/K/N to add up the section totals.

For the full scope, I will also need formulas added to the financial summary tab in columns E/F/G/N that will pull from the total row for each quote. Information in the first row (row 4) would pull the total for the first quote in the BOM in tab. Second line would pull the information from the second quote and so on.

[nankw83]

Hi Sawyer84 & welcome to the forum

Try below code ...

Sub test()

Dim a, Col$

With Sheets("BOM")
   With .Range("D1", .Range("D" & Rows.Count).End(3))
      a = Filter(.Parent.Evaluate(Replace("transpose(iferror(if(search(""section"",@)=1,row(@)),false))", "@", .Address)), False, 0)
      If UBound(a) = -1 Then Exit Sub
      ReDim Preserve a(0 To UBound(a) + 1)
      a(UBound(a)) = .Rows.Count
   End With
   For x = 0 To UBound(a) - 1
      For i = 1 To 4
         Col = Choose(i, "G", "I", "K", "N")
         .Cells(a(x), Col) = "=sum(" & Col & a(x) + 1 & ":" & Col & a(x + 1) - 1 & ")"
      Next
   Next
End With

End Sub

[Sawyer84]

nankw83 Thank you so much for the quick reply. The formula works ALMOST perfectly! The only issue I see is the sum formula in the last Section before the Option Section. In those formulas, they include the Option Sections and Rental Sections in their totals.
There also isn't a formula for the Option Sections, but I figured once we get the Section formulas down I'll try to recreate that for the Option Sections.

[nankw83]

I went by the blue colored rows ... So formulas should be in rows where there's 'SECTION*', 'OPTION SECTION' & 'RENTAL SECTION' ?

[Sawyer84]

Just 'SECTION*' and 'OPTION SECTION', no totals needed for 'RENTAL SECTION'. The range will need to be C rather than D because the description in column D will change sometimes, but column C will never change.

[nankw83]

This is weird ... I posted an updated code yesterday but it disappeared Anyway, try below code

Sub test()

Dim a, Col$

With Sheets("BOM")
   With .Range("C1", .Range("C" & Rows.Count).End(3))
      a = Filter(.Parent.Evaluate(Replace("transpose(if(isnumber(search(""rental"",@)),false,iferror(if(search(""section"",@)>0,row(@)),false)))", "@", .Address)), False, 0)
      If UBound(a) = -1 Then Exit Sub
      ReDim Preserve a(0 To UBound(a) + 1)
      a(UBound(a)) = .Rows.Count
   End With
   For x = 0 To UBound(a) - 1
      For i = 1 To 4
         Col = Choose(i, "G", "I", "K", "N")
         .Cells(a(x), Col) = "=sum(" & Col & a(x) + 1 & ":" & Col & a(x + 1) - 1 & ")"
      Next
   Next
End With

End Sub

[Sawyer84]

That is weird because I can still see your previous code. Anyway, I tried the code and it added the sum formulas to the option sections. However, the last option section in each quote goes past the last part in that option section. I've attached a couple snapshots to help explain.

[nankw83]

The code simply looks for the cells containing the word "section" & consider the formula for the cells in between .. Does it provide incorrect numbers in your first picture ? In the second picture, it includes the "Rental Section" but the only flaw I have is the last formula should include row #34 as well

[Sawyer84]

No sir, the option section formula should only include the parts in the specific option section. So basically, it would need stop before the next bold section. So in snapshot 1 that formula would need to stop at row #14. The formula in snapshot 2 would need to stop at row #29. Rental sections don?t need to be totaled, but if it will make this VBA code easier, totaling the rental section won?t be an issue.
I know this hasn?t been easy, but I really appreciate your patience and willingness to help me.

[oeldere]

@Sawyer84

Probably I can't help you enough, but more general.


It would be very usefull is you add the expected result manualy in the file.

Then forummembers can compair the offered solution with the manualy expected result.

[Sawyer84]

I don?t have access to that file right now since I?m not at home, but see below for what the formula should be for rows that are incorrect. I just don?t want to cause confusion because those formulas will change for every quote that is done, since the number of rows will always be different.

Row 12, Cell G12: =SUM(G13:G14)
Row 34, Cell G34: =SUM(G28:G29)

[beyond Excel]

Row 34, Cell G34: =SUM(G28:G29)

Hello. Sawyer84.
Your clarification didn't help much because you were wrong to mention row 34.

What oeldere asked you to do is upload a second workbook to the Forum that has three sheets:

- BOM,
- Co. Cost, and
- expected BOM

In this last sheet you will place the expected formulas in the cells: G24, G27, G30 and G33.

[Sawyer84]

Goodness, yes, you?re right. That?s what I get for trying to do this on my phone. Correction below. I?ll try to get that spreadsheet uploaded when I get home later today. Rental Sections on rows 30 and 33 don?t need a formula, but if it makes creating the correct VBA code easier, having formulas for the rental sections won?t be an issue.

Row 12, Cell G12: =SUM(G13:G14)
Row 27, Cell G27: =SUM(G28:G29)

[Sawyer84]

Okay, I think I figured out a way to make the VBA code that nankw83 created. The only formula that is slightly wrong is the very last section on the very last quote. So that will be easy enough to fix or just ignore since the last section is typically a rental section, which don't need a formula anyway. I will mark this as solved. I tried to post the code, but it wouldn't let me, so I've attached a text file and a snapshot.

nankw83 I am incredibly thankful for all of your help with this.

[beyond Excel]

Mmm...
I see that you assume that the value of cell G7 should be 3.

I ask you: shouldn't it be 10 that represents the sum of all Section B (G8 to G14)?

[Sawyer84]

Negative, Section B only consists of the 2 rows below it, rows 8 and 9. Basically, every row that is in bold is it's own section. I've attached a snapshot of what needs to happen next, which I was going to post in a new thread. I just wanted to mention it to you so you have the full picture and may help explain things. Only rows that have Section A, Section B, Section C, etc., in column C, are included in the total. Anything that has Option Section or Rental Section in column C, aren't included in the total. Like I said, my next thread will be about creating a VBA code that will insert 2 rows above the first option section in every quote and that puts the Total text in column D and formulas in columns G/I/K/N that adds the section totals above it. I already have the conditional formatting that will bold and color a row with the text Total in column D

[beyond Excel]

And why didn't you put it all together?... What a waste of time!

For your new query, publish only three sheets:

- BOM
- Co.Cost
- expected BOM

- The first sheet is the one you originally have. I have a suspicion that in your original data you don't have all those empty rows that you showed in your first post.

- And on the third page you show us what you hope to achieve.

[Sawyer84]

That wasn?t my goal and I apologize if I wasted anyone?s time. I mentioned in my very first post (2nd to last paragraph) that my next goal would be inserting a row that calculates the total. I know my first post was long, so I can understand you not making it all the way to the end to see that information.

[Sawyer84]

I've attached the spreadsheet with the sheets like you asked. The original data doesn't have the rows inserted between the quotes, I created a VBA code to do that. However, I found it made the formula work easier if I added the rows between quotes after the sections were totaled.

[beyond Excel]

As this thread is already finished and -probably- nothing that we have helped you will serve the purposes of the new requirement you have (the most important thing is that your original data does not come with empty rows as I imagined), then you better start a new thread and upload this last workbook.

Tip: Don't rush to end the thread or give reputation early!

[jindon]

Sawyer84,

try

Sub test()
    Dim x, i As Long, s
    Application.ScreenUpdating = False
    With Sheets("bom").Cells(1).CurrentRegion
        x = Filter(.Parent.[transpose(if((left(c3:c10000,7)="OPTION ")+(right(c3:c10000,1)="A")+(row(3:10000)=3),row(3:10000)))], False, 0)
        For i = UBound(x) To 1 Step -1
            s = .Cells(x(i), 3)
            .Rows(x(i)).Resize(2).Insert
            If s Like "OPTION *" Then
                With .Rows(x(i))
                    .Font.Bold = True: .Interior.Color = rgbDarkOrange
                    .Range("d1") = "TOTAL": .Range("d1").HorizontalAlignment = xlRight
                    .Range("g1,i1,k1,n1:p1").FormulaR1C1 = "=sum(r" & x(i - 1) & "c:r[-1]c)"
                End With
            End If
        Next
    End With
    Application.ScreenUpdating = True
End Sub

[Sawyer84]

jindon, thank you for that. It is close to working perfectly. The only thing it didn't do correctly was the total formula. Instead of adding only the section totals (rows 3 & 7), it added everything. I've attached a snapshot of what it did (1st snapshot) and then what it should be doing (2nd snapshot).

[jindon]

Didn't notice the formula in top of each SECTION.
try

Sub test()
    Dim x, i As Long, s
    Application.ScreenUpdating = False
    With Sheets("bom").Cells(1).CurrentRegion
        x = Filter(.Parent.[transpose(if((isnumber(search("SECTION",c3:c10000)))+(row(3:10000)=3)+(row(3:10000)=max(if(c3:c10000<>"",row(3:10000)))),row(3:10000)))], False, 0)
        For i = 0 To UBound(x) - 1
            If UCase$(.Cells(x(i), 3)) Like "SECTION *" Then
                .Rows(x(i)).Range("g1,i1,k1,n1:p1").FormulaR1C1 = "=subtotal(9,r" & x(i) + 1 & "c:r" & x(i + 1) - 1 & "c)"
            End If
        Next
        x = Filter(.Parent.[transpose(if((left(c3:c10000,7)="OPTION ")+(right(c3:c10000,1)="A")+(row(3:10000)=3),row(3:10000)))], False, 0)
        For i = UBound(x) To 1 Step -1
            s = .Cells(x(i), 3)
            .Rows(x(i)).Resize(2).Insert
            If s Like "OPTION *" Then
                With .Rows(x(i))
                    .Font.Bold = True: .Interior.Color = rgbDarkOrange
                    .Range("d1") = "TOTAL": .Range("d1").HorizontalAlignment = xlRight
                    .Range("g1,i1,k1,n1:p1").FormulaR1C1 = "=subtotal(9,r" & x(i - 1) & "c:r[-1]c)"
                End With
            End If
        Next
    End With
    Application.ScreenUpdating = True
End Sub

[Sawyer84]

Holy smokes, that worked perfectly!! Thank you so much!

[jindon]

You are welcome and thanks for the rep,

When you need to Sum only the summed row(s) within the specific range, Subtotal function should be the way to calculate.

[Sawyer84]

That actually makes a lot of sense after seeing how you did that. I appreciate the tip.

[Sawyer84]

jindon, after a second glance I had to modify your code slightly since it was putting the subtotal in columns O and P. It works, but columns O and P in the new Total row are blank. Is there anyway to modify this code to copy the formula either above or below? See below for the pre-loaded formula in those columns.

Column O formula: =(N10-G10)/N10
Column P formula: =(N10-I10)/N10

Sub test()

    Dim x, i As Long, s
    Application.ScreenUpdating = False
    With Sheets("bom").Cells(1).CurrentRegion
        x = Filter(.Parent.[transpose(if((isnumber(search("SECTION",c3:c10000)))+(row(3:10000)=3)+(row(3:10000)=max(if(c3:c10000<>"",row(3:10000)))),row(3:10000)))], False, 0)
        For i = 0 To UBound(x) - 1
            If UCase$(.Cells(x(i), 3)) Like "SECTION *" Then
                .Rows(x(i)).Range("g1,i1,k1,n1").FormulaR1C1 = "=subtotal(9,r" & x(i) + 1 & "c:r" & x(i + 1) - 1 & "c)"
            End If
        Next
        x = Filter(.Parent.[transpose(if((left(c3:c10000,7)="OPTION ")+(right(c3:c10000,1)="A")+(row(3:10000)=3),row(3:10000)))], False, 0)
        For i = UBound(x) To 1 Step -1
            s = .Cells(x(i), 3)
            .Rows(x(i)).Resize(2).Insert
            If s Like "OPTION *" Then
                With .Rows(x(i))
                    .Font.Bold = True: .Interior.Color = rgbDarkOrange
                    .Range("d1") = "TOTAL": .Range("d1").HorizontalAlignment = xlRight
                    .Range("g1,i1,k1,n1").FormulaR1C1 = "=subtotal(9,r" & x(i - 1) & "c:r[-1]c)"
                End With
            End If
        Next
    End With
    Application.ScreenUpdating = True
    
End Sub

[Sawyer84]

@jindon, sorry for the back to back messages, but I just tried this your original code on an existing spreadsheet with multiple quotes and it didn't insert the Total rows correctly, see snapshot below. Often times there are multiple options sections in one quote and I think that is what's causing this issue.
Snapshot 1 (Quote 1): Rows 15 and 16 shouldn't be there
Snapshot 2 (Quote 4): Rows 42 and 43 shouldn't be there

[jindon]

See if this works as you want.

Sub test()
    Dim x, i As Long, s(1)
    Application.ScreenUpdating = False
    With Sheets("bom").Cells(1).CurrentRegion
        x = Filter(.Parent.[transpose(if((isnumber(search("SECTION",c3:c10000)))+(row(3:10000)=3)+(row(3:10000)=max(if(c3:c10000<>"",row(3:10000)))),row(3:10000)))], False, 0)
        For i = 0 To UBound(x) - 1
            If UCase$(.Cells(x(i), 3)) Like "SECTION *" Then
                .Rows(x(i)).Range("o1:p1").FormulaR1C1 = Array("=(rc[-1]-rc7)/rc[-1]", "=(rc[-2]-rc9)/rc[-2]")
                .Rows(x(i)).Range("g1,i1,k1,n1").FormulaR1C1 = "=subtotal(9,r" & x(i) + 1 & "c:r" & x(i + 1) - 1 & "c)"
            End If
        Next
        x = Filter(.Parent.[transpose(if((left(c3:c10000,7)="OPTION ")+(right(c3:c10000,1)="A")+(row(3:10000)=3),row(3:10000)))], False, 0)
        For i = UBound(x) To 1 Step -1
            s(0) = .Cells(x(i), 3): s(1) = .Cells(x(i - 1), 3)
            If (s(0) Like "* A") + (s(1) Like "* A") Then
                .Rows(x(i)).Resize(2).Insert
                If s(0) Like "OPTION *" Then
                    With .Rows(x(i))
                        .Font.Bold = True: .Interior.Color = "&H8CFF"
                        .Range("d1") = "TOTAL": .Range("d1").HorizontalAlignment = xlRight
                        .Range("o1:p1").FormulaR1C1 = Array("=(rc[-1]-rc7)/rc[-1]", "=(rc[-2]-rc9)/rc[-2]")
                        .Range("g1,i1,k1,n1").FormulaR1C1 = "=subtotal(9,r" & x(i - 1) & "c:r[-1]c)"
                    End With
                End If
            End If
        Next
    End With
    Application.ScreenUpdating = True
End Sub

[Sawyer84]

That looks like a winner. I'll continue to keep testing it. Thanks again for all of your help.

[Sawyer84]

@jindon, I did find one issue. Sometimes there is only 1 section on a quote, it's typically Section A, but sometimes it might be a different Section. In this scenario, I had a spreadsheet that had a quote with only Section D and it wouldn't put spaces between the quote above it, or add the total row. See snapshot below. There should be 2 rows above row 18 to separate quote 1 and quote 2 and then there should be 2 rows above row 21, which include the total.

[jindon]

I will have a look at it sometime tomorrow, hopefully.

[Sawyer84]

Thank you very much

[jindon]

Try this one

Sub test()
    Dim a, x, i As Long, s(1)
    Application.ScreenUpdating = False
    With Sheets("bom").Cells(1).CurrentRegion
        a = .Value: s(1) = .Parent.Evaluate("max(if(" & .Columns(3).Address & "<>"""",row(" & .Address & ")))")
        With CreateObject("Scripting.Dictionary")
            For i = 3 To UBound(a, 1)
                If (a(i, 3) Like "SECTION *") + (a(i, 3) Like "OPTION *") Then
                    s(0) = Join(Array(a(i, 1), Split(a(i, 3))(0)), Chr(2))
                    If Not .exists(s(0)) Then .Item(s(0)) = i
                End If
            Next
            .Item(i) = s(1)
            x = .items
        End With
        For i = 0 To UBound(x) - 1
            If UCase$(.Cells(x(i), 3)) Like "SECTION *" Then
                .Rows(x(i)).Range("o1:p1").FormulaR1C1 = Array("=(rc[-1]-rc7)/rc[-1]", "=(rc[-2]-rc9)/rc[-2]")
                .Rows(x(i)).Range("g1,i1,k1,n1").FormulaR1C1 = "=subtotal(9,r" & x(i) + 1 & "c:r" & x(i + 1) - 1 & "c)"
            End If
        Next
        For i = UBound(x) To 1 Step -1
            s(0) = .Cells(x(i), 3): s(1) = .Cells(x(i - 1), 3)
            If (s(0) Like "SECTION*") + (s(1) Like "SECTION*") Then
                .Rows(x(i)).Resize(2).Insert
                If s(0) Like "OPTION *" Then
                    With .Rows(x(i))
                        .Font.Bold = True: .Interior.Color = "&H8CFF"
                        .Range("d1") = "TOTAL": .Range("d1").HorizontalAlignment = xlRight
                        .Range("o1:p1").FormulaR1C1 = Array("=(rc[-1]-rc7)/rc[-1]", "=(rc[-2]-rc9)/rc[-2]")
                        .Range("g1,i1,k1,n1").FormulaR1C1 = "=subtotal(9,r" & x(i - 1) & "c:r[-1]c)"
                    End With
                End If
            End If
        Next
    End With
    Application.ScreenUpdating = True
End Sub

[Sawyer84]

Okay, so I'm kind of lost now and I will COMPLETELY understand if you want to give up on this. I know you've already spent a lot of your time on this. However, if you are still willing to help, that would be incredible. So the issue with your code is the first section in each quote includes the total of the entire quote, so it acts just like the total row. The other issue is that there isn't a formula for each section and option section. So, I modified your other code below and the only issue with this code is that it only works correctly if the first section in a quote is Section A. If it could be modified to work if the first section in a quote is Section B, Section C, Section D, Section E, Section F, or Section G, then that would be great.

Sub test()
    Dim x, i As Long, s(1)
    Application.ScreenUpdating = False
    With Sheets("bom").Cells(1).CurrentRegion
        x = Filter(.Parent.[transpose(if((isnumber(search("SECTION",c3:c10000)))+(row(3:10000)=3)+(row(3:10000)=max(if(c3:c10000<>"",row(3:10000)))),row(3:10000)))], False, 0)
        For i = 0 To UBound(x) - 1
            If UCase$(.Cells(x(i), 3)) Like "SECTION *" Then
                .Rows(x(i)).Range("o1:p1").FormulaR1C1 = Array("=(rc[-1]-rc7)/rc[-1]", "=(rc[-2]-rc9)/rc[-2]")
                .Rows(x(i)).Range("g1,i1,k1,n1").FormulaR1C1 = "=subtotal(9,r" & x(i) + 1 & "c:r" & x(i + 1) - 1 & "c)"
            End If
        Next
        For i = 0 To UBound(x) - 1
            If UCase$(.Cells(x(i), 3)) Like "OPTION *" Then
                .Rows(x(i)).Range("o1:p1").FormulaR1C1 = Array("=(rc[-1]-rc7)/rc[-1]", "=(rc[-2]-rc9)/rc[-2]")
                .Rows(x(i)).Range("g1,i1,k1,n1").FormulaR1C1 = "=subtotal(9,r" & x(i) + 1 & "c:r" & x(i + 1) - 1 & "c)"
            End If
        Next
        x = Filter(.Parent.[transpose(if((left(c3:c10000,7)="OPTION ")+(right(c3:c10000,1)="A")+(row(3:10000)=3),row(3:10000)))], False, 0)
        For i = UBound(x) To 1 Step -1
            s(0) = .Cells(x(i), 3): s(1) = .Cells(x(i - 1), 3)
            If (s(0) Like "* A") + (s(1) Like "* A") Then
                .Rows(x(i)).Resize(2).Insert
                If s(0) Like "OPTION *" Then
                    With .Rows(x(i))
                        .Font.Bold = True:
                        .Range("d1") = "TOTAL": .Range("d1").HorizontalAlignment = xlRight
                        .Range("o1:p1").FormulaR1C1 = Array("=(rc[-1]-rc7)/rc[-1]", "=(rc[-2]-rc9)/rc[-2]")
                        .Range("g1,i1,k1,n1").FormulaR1C1 = "=subtotal(9,r" & x(i - 1) & "c:r[-1]c)"
                    End With
                End If
            End If
        Next
    End With
    Application.ScreenUpdating = True
End Sub

[jindon]

Working with picture is too hard.
If you upload a workbook with all the possible situations that will occur and the result that you want, I will look at it tomorrow.
If you don't want to do it, just ignore this post.

[Sawyer84]

I will absolutely do that. I will upload it tomorrow morning my time, which will probably towards the end of your day.

[Sawyer84]

Okay, I've uploaded the spreadsheet. The 'Expected BOM' is what I would like it to look like.

[jindon]

Let's see if I missed anything.
This code also accepts with or without any OPTION block(s) within a QUOTE.

Sub test()
    Dim a, x, y, t, i As Long, flg As Boolean
    Application.ScreenUpdating = False
    With Sheets("bom").Cells(1).CurrentRegion
        x = Filter(.Parent.[transpose(if((isnumber(search("SECTION",c3:c10000)))+(row(3:10000)=3)+(row(3:10000)=max(if(c3:c10000<>"",row(3:10000)))),row(3:10000)))], False, 0)
        a = .Resize(x(UBound(x))).Value
        For i = 0 To UBound(x) - 1
            If UCase$(.Cells(x(i), 3)) Like "SECTION *" Then
                .Rows(x(i)).Range("o1:p1").FormulaR1C1 = Array("=(rc[-1]-rc7)/rc[-1]", "=(rc[-2]-rc9)/rc[-2]")
                .Rows(x(i)).Range("g1,i1,k1,n1").FormulaR1C1 = "=subtotal(9,r" & x(i) + 1 & "c:r" & x(i + 1) - 1 & "c)"
            Else
                If .Cells(x(i), 3) Like "OPTION *" Then
                    y = .Parent.Evaluate("min(if((isnumber(search(""SECTION"",c3:c10000)))*(row(3:10000)>" & x(i) & "),row(3:10000)))")
                    If y = 0 Then y = UBound(a, 1)
                    .Rows(x(i)).Range("o1:p1").FormulaR1C1 = Array("=(rc[-1]-rc7)/rc[-1]", "=(rc[-2]-rc9)/rc[-2]")
                    .Rows(x(i)).Range("g1,i1,k1,n1").FormulaR1C1 = "=subtotal(9,r" & x(i) + 1 & "c:r" & y - 1 & "c)"
                End If
            End If
        Next
        With CreateObject("Scripting.Dictionary")
            For i = UBound(a, 1) To 3 Step -1
                If a(i, 1) <> "" Then
                    If Not .exists(a(i, 1)) Then .Item(a(i, 1)) = Array(0, i, i)
                    If a(i, 3) Like "OPTION *" Then
                        .Item(a(i, 1)) = Array(.Item(a(i, 1))(0), i, .Item(a(i, 1))(2))
                    ElseIf a(i, 3) Like "SECTION *" Then
                        .Item(a(i, 1)) = Array(i, .Item(a(i, 1))(1), .Item(a(i, 1))(2))
                    End If
                End If
            Next
            x = .items
        End With
        For i = 0 To UBound(x)
            If i > 0 Then .Rows(x(i)(2) + 1).Resize(2).Insert: t = x(i)(2) + 1
            If x(i)(1) <> x(i)(2) Then
                .Rows(x(i)(1)).Resize(2).Insert: t = x(i)(1)
            Else
                t = x(i)(2) + 1
            End If
            With .Rows(t)
                .Font.Bold = True: .Interior.Color = "&H8CFF"
                .Range("d1") = "TOTAL": .Range("d1").HorizontalAlignment = xlRight
                .Range("o1:p1").FormulaR1C1 = Array("=(rc[-1]-rc7)/rc[-1]", "=(rc[-2]-rc9)/rc[-2]")
                .Range("g1,i1,k1,n1").FormulaR1C1 = "=subtotal(9,r" & x(i)(0) & "c:r[-1]c)"
            End With
        Next
    End With
    Application.ScreenUpdating = True
End Sub

[Sawyer84]

Awesome, I'm checking it out now.

[Sawyer84]

Well it's official, you're a genius! lol. For real, I've ran some tests on real life quotes and I haven't found an issue. I literally can't thank you enough for all of your hard work, I don't believe a lot of people would go above and beyond like you did. Thanks again.