Formatting rows when using Subtotals
MZ Tools makes life easier for the Excel VBA coder
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: Formatting rows when using Subtotals

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I am preparing a macro for a report that includes subtotals using the subtotal command. However, it is difficult to look at, Excel does a lousy job of formatting it. Is there a way to automatically format the subtotal rows, so that it separates the groups a bit more nicely? Perhaps just to change the color of the subtotal rows? Thanks!

  2. #2
    New Member
    Join Date
    Mar 2002
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Don't tell me I actually stumped everyone! Surely there is a way to format the subtotal rows?!?

  3. #3
    Board Regular eliW's Avatar
    Join Date
    Mar 2002
    Posts
    1,919
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-08 13:12, DavidC wrote:
    Don't tell me I actually stumped everyone! Surely there is a way to format the subtotal rows?!?
    I'd expxcted the *gurus* to comment on this question, but as no one did I'll try my best.

    As the subtotal cells are actually formulas, use this to locate them and paint the cell.

    You can include in your macro: Edit|Goto|Special|Formula to locate thease cells and then paint them or the entire row.

    Hope this helps,
    Eli

  4. #4
    New Member
    Join Date
    Apr 2002
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-08 12:23, DavidC wrote:
    I am preparing a macro for a report that includes subtotals using the subtotal command. However, it is difficult to look at, Excel does a lousy job of formatting it. Is there a way to automatically format the subtotal rows, so that it separates the groups a bit more nicely? Perhaps just to change the color of the subtotal rows? Thanks!

    Display level 2 rows.
    Go to Edit>Special>VisibleCells>Only.
    Format as required.

  5. #5
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,136
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    DavidC:

    Since you asked for an automated solution here's one. The code looks a bit lengthy at first but it formats using a union range, not just one row at a time, to save a little system effort. Modify for subtotal column and format options (color index, bold etc). Thanks to Celia from a while back with the foundation of this code.

    By the way, regarding your second post as follow-up to your first less than an hour before, some of us have lives during the day and can't get to these questions until this lovely hour of the morning. But we do what we can to assist. Here's the code to get you started.

    ''''''''''''''''''''''''''

    Sub SubtotalFormat()
    Application.ScreenUpdating = False
    Dim myCol As Range, cell As Range, RtoSel As Range
    Dim LtoSel As String
    Set myCol = Range(Range("A1"), Range("A65536").End(xlUp))
    LtoSel = "Total"
    For Each cell In myCol
    If Right(cell, 5) = LtoSel Then
    If RtoSel Is Nothing Then
    Set RtoSel = cell
    Else
    Set RtoSel = Application.Union(RtoSel, cell)
    End If
    End If
    Next
    With RtoSel.EntireRow
    .Interior.ColorIndex = 3
    .Font.FontStyle = "Bold"
    End With
    [A1].Select
    Application.ScreenUpdating = True
    End Sub

    ''''''''''''''''''''''''''''''

  6. #6
    New Member
    Join Date
    Apr 2002
    Location
    Plano, Texas
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Tom,

    Where and how in the example you listed above could I insert a row after each subtotal row?

    Any info will be greatly appreciated.

    Thanks,

    Larry

  7. #7
    New Member
    Join Date
    Apr 2002
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-09 09:08, Larry wrote:
    Tom,

    Where and how in the example you listed above could I insert a row after each subtotal row?

    Any info will be greatly appreciated.

    Thanks,

    Larry

    Sub SubtotalFormat()
    Application.ScreenUpdating = False
    Dim myCol As Range, cell As Range, RtoSel As Range
    Dim LtoSel As String
    Range("A65536").End(xlUp).EntireRow.Insert
    Set myCol = Range(Range("A1"), Range("A65536").End(xlUp))
    LtoSel = "Total"
    For Each cell In myCol
    If Right(cell, 5) = LtoSel Then
    If RtoSel Is Nothing Then
    Set RtoSel = cell
    Else
    Set RtoSel = Application.Union(RtoSel, cell)
    End If
    End If
    Next
    With RtoSel.EntireRow
    .Offset(1, 0).Insert
    .Interior.ColorIndex = 3
    .Font.FontStyle = "Bold"
    End With
    Range("A65536").End(xlUp)(0, 1).EntireRow.Delete
    [A1].Select
    Application.ScreenUpdating = True
    End Sub

  8. #8
    New Member
    Join Date
    Apr 2002
    Location
    Plano, Texas
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    FGHT,

    That worked for the first subtotal, but it stops after the first. What am I missing to have it insert a row after all subtotals?

    Thanks,

    Larry

    [ This Message was edited by: Larry on 2002-04-09 12:26 ]

  9. #9
    New Member
    Join Date
    Apr 2002
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-09 12:24, Larry wrote:
    FGHT,

    That worked for the first subtotal, but it stops after the first. What am I missing to have it insert a row after all subtotals?

    Thanks,

    Larry

    [ This Message was edited by: Larry on 2002-04-09 12:26 ]

    It works for me.

  10. #10
    New Member
    Join Date
    Apr 2002
    Location
    Plano, Texas
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com