Formatting of Menu/Data/Subtotals

shdawson

shdawson

Active Member
Joined
Jan 6, 2007
Messages
381
Hi,


Not sure this is the place to ask, but will start here.

Have a massive report that would be nicely served by the Subtotals option on the menu. My question is about formatting of that data. After the *many* rows subtotal, would be nice to bold those rows. The said row will change with each report run, so it is a manual thing now.

Is there a cool plugin out there on the web or something that would assist with the formatting of the subtotaled information? Or, anther trick?


Thank You,
SHD
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Sort by date Sort by votes
Hi.

If you are using Subtotals try this to bold the entire row

Code: 
Sub BoldSbttls()
Dim i As Long, LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 3 To LR
    If Range("A" & i).Value Like "* Total" Then Rows(i).Font.Bold = True
Next i
End Sub
 
Upvote 0
To use the code, press ALT + F11 to open the Visual Basic Editor, select Module from the Insert menu then paste the code into the white space on the right. Press ALT + Q to close the code window.

Select the sheet that you want to format, press ALT + F8, click on BoldSbttls then click the Run button.

This will work for any sheet in the workbook that contains the code.
 
Upvote 0
Thank you for the code and the instructions to properly load it into the workbook.

I did so, and saved the workbook. However, the code does not change the formatting. Can you assit to evalaute the problem, please?


Thanks,
SHD
 
Upvote 0
Something else you can do to spice up the formatting is after you apply the subtotals and use Peter's code to bold the total rows, you can apply a table format. Note that you have to do it after the subtotals.

HTH,
 
Upvote 0
What column are the "XXX Total"'s in? If it's not A, then the code will need to be adjusted.
 
Upvote 0
Ah...thought it was a range or something like that. Changed column to B, and all works well. QUESTION: What is the best way to add a comment inside of the provided code so I remember this in the future???

I will further consider the Table formatting aspect.


Thank you, all. Happy Holidays to everyone!




Kindly,
SHD
 
Upvote 0
You can comment the code like this

Code: 
Sub BoldSbttls()
' Change A to the column with the subtotals (twice)
Dim i As Long, LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 3 To LR
    If Range("A" & i).Value Like "* Total" Then Rows(i).Font.Bold = True
Next i
End Sub
 
Upvote 0
OK...thought so. Was thinking perhaps Excel had a wiz-bang way to manage comments, so wanted to ask those in the know.


Thanks Again,
SHD
 
Upvote 0
All you need to do to add a comment is precede the comment text with an apostrophe.

Just note that you can't comment within a procedural call, so you can't do this:

For i = 3 to ' Some comment here LR

But you can do:

For i = 3 to LR ' Some comment here
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Simply current workload. Pivot table?
Replies
0
Views
180
ShadowRider
S
S
  • Question Question
Excel VBA - Sort & Get Totals - Logic
Replies
4
Views
419
STARL0RD
S
W
  • Question Question
Formatting raw data with extra rows automatically added in-between records
Replies
1
Views
73
Peter_SSs
Peter_SSs
C
  • Question Question
Issues subtotaling and breaking down the subtotal outline to obtain records for copying.
Replies
0
Views
579
Chris101
C
F
  • Question Question
VBA SUM and Subtotal
Replies
4
Views
324
freeveil
F

Forum statistics

Threads
1,224,621
Messages
6,179,941
Members
452,949
Latest member
beartooth91

Share this page

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