Consulting

Results 1 to 11 of 11

Thread: VBA Code to add groupings to subtotals

  1. #1

    VBA Code to add groupings to subtotals

    I have code that creates subtotals. Is there a way to group (i.e. Data tab Group function) each subtotal with VBA? Any ideas?

    Here is the code for subtotals...

    i = 3
    J = i
    'Sort the data so like data is grouped together.
    Range("A5").CurrentRegion.Offset(1).Sort Range("A12"), 1
    'Loops throught Col A Checking for match then when there is no match add Sum
    Do While Range("A" & i) <> ""
    If Range("A" & i) <> Range("A" & (i + 1)) Then
    Rows(i + 1).Insert
    Range("A" & (i + 1)) = "Subtotal " & Range("A" & i).Value
    For iCol = 13 To 73 'Columns to Sum
    Range(Cells(i + 1, 13), Cells(i + 1, 73)).FormulaR1C1 = "=SUBTOTAL(9,R" & J & "C:R[-1]C)"
    Next iCol
    Range(Cells(i + 1, 1), Cells(i + 1, 73)).Font.Bold = True
    Range(Cells(i + 1, 1), Cells(i + 1, 73)).BorderAround ColorIndex:=1




    i = i + 2
    J = i
    Else
    i = i + 1
    End If
    Loop

  2. #2
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Have another go at attaching a file.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  3. #3

    With attached example

    p45cal,

    I have attached a simple example of what I am looking to accomplish. My goal is to create the subtotals with the VBA look included in this thread. But to then create these + and - grouping for the rows for each of those subtotals.

    Thank you.

    Steve
    Attached Files Attached Files

  4. #4
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    After the BorderAround line add:
    Rows(j & ":" & i).Rows.Group

    Your code doesn't seem to apply very well to your sample data. Every line is different. Your summing blank columns.

    ps. You don't need the For iCol = 13 To 73 loop, just keep the single line in the middle of the loop.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  5. #5
    p45cal,

    Thanks for the reply. Good catch. But actually I sent you just a small sample. My worksheet is large so I just sent a sample. In the version that I am using it needs to sum those columns. Sorry I was not clear on that point.

    I am just searching for a way to add in groupings for each subtotal (the + and - on the left side, left of the row numbers). Not sure if that is possible with VBA?

    Any ideas?
    Steve

  6. #6
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    The first 2 lines of my last response (msg#4) do that.
    With the data you supplied in column A, every row is different from the row above it so it will give subtotals after every row and you may not get grouping of single rows.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  7. #7

    I have attached a better Worksheet example

    p45cal,

    This an example of the entire worksheet that I am trying to group by subtotals with VBA. Does this help? Or is it not possible to do this with VBA?

    Thanks.
    Steve
    Attached Files Attached Files

  8. #8
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Why don't you do as I suggest?!
    The attached has that one line added. Run the amended-as-suggested macro by clicking the button on the sheet at cell H2. I've removed existing subtotals and cleared the existing outline (grouping) from the sheet.

    Note that you have placed a completely blank column at column AA so the currentregion part of the line:
    Range("A5").CurrentRegion.Offset(1).Sort Range("A12"), 1
    means that sorting only takes place to the left of that column. Columns to the right of that column will remain as they were.
    Attached Files Attached Files
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  9. #9
    p45cal,

    AWESOME! Thanks so much. Solved.

    As far as the sort. Maybe I am not sure what I am doing. I can populate AA but maybe the range A5 and A12 doesn't make sense?

  10. #10
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    You only need to populate one cell preferably the header row at AA2; put the word blank or something in it, and if you don't want to see it make its font white.
    Consider whether you need to do the same at AD2 and BU2.

    As for A5 and A12, it should be alright in this case but:
    Range("A2").CurrentRegion.Offset(1).Sort Range("A2"), 1
    might be better.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  11. #11
    Ok. Thanks again.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •