Formatting rows when using Subtotals

DavidC

New Member
Joined
Mar 17, 2002
Messages
26
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!
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Don't tell me I actually stumped everyone! Surely there is a way to format the subtotal rows?!?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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

''''''''''''''''''''''''''''''
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,952
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top