Formatting of Menu/Data/Subtotals

[shdawson]

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

 

[VoG]

Hi.

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

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

 

[VoG]

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.

 

[shdawson]

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

 

[Smitty]

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,

 

[Smitty]

What column are the "XXX Total"'s in? If it's not A, then the code will need to be adjusted.

 

[shdawson]

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

 

[VoG]

You can comment the code like this

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

 

[shdawson]

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

 

[Smitty]

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