2

Here's the problem. When there is only one row in a subtotal grouping:

  • the inserted row doesn't come in at the right outlinelevel.
  • the subtotal doesn't automatically include the inserted cell.

And here's the code that inserts the row (i is defined earlier):

           For j = 2 To lEndRow * (1.5)
                If InStr(Cells(j, i), "Total") Then
                    Cells(j - 1, i).EntireRow.Insert
                    With Cells(j - 1, i)
                        .EntireRow.Font.ColorIndex = 3
                        .EntireRow.Interior.ColorIndex = 2
                    End With
                    Cells(j - 1, i).EntireRow.OutlineLevel = 2 ' This didn't work,
                         ' it puts all the inserted rows at 2 but doesn't group it
                         ' the subtotal.
                    Cells(j - 1, i + 8) = "1% Discount within terms"
                    Cells(j - 1, i + 24).FormulaR1C1 = "=Round((R[2]C[-8])*(.01),2)"
                    j = j + 1
                End If
            Next

I imagine this is an easy problem if you know it. I just don't know it and it's gotten me pretty frustrated. Happy first post to me and happy holidays to you.

Community
  • 1
  • 1
Bippy
  • 95
  • 1
  • 3
  • 12

1 Answers1

1

This is a guess but I think it is worth a try.

From MS Help's About outlining a worksheet

  • "Data to be outlined should be in range, where each column has a label in the first row and contains similar facts, and there are no blank rows or columns [my highlighting] within the range."

At the time you set the outline, the sub-total row is blank so cannot be part of the range. Try:

             Cells(j - 1, i + 8) = "1% Discount within terms"
             Cells(j - 1, i + 24).FormulaR1C1 = "=Round((R[2]C[-8])*(.01),2)"
             Cells(j - 1, i).EntireRow.OutlineLevel = 2

Best of luck.

Tony Dallimore
  • 12,335
  • 7
  • 32
  • 61
  • My apologies, I didn't know anyone replied until I came back today, regarding another problem. Thanks much and I'll try it after this new stumbling block. Responded Christmas Eve none the less.... thanks. – Bippy Jan 07 '12 at 22:05
  • I am not quite the fanatic I appear to be. All our children were having Christmas with their partner's parents. So for us the really busy day was delayed until the 28th. – Tony Dallimore Jan 08 '12 at 12:23