Bold lines that contains 'Total'

fddekker

Board Regular
Joined
Jun 30, 2008
Messages
63
I often make use of Excel's built-in 'Subtotal' function.

What is the most economic code to (i) find the rows that contain the subtotals, (ii) delete the word 'Total', and (iii) bold the rows that contain the subtotals?

Thanks in advance!!
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,064
Office Version
  1. 365
Platform
  1. Windows
The whole process could be done pretty quickly manually - do you really need code?
 

fddekker

Board Regular
Joined
Jun 30, 2008
Messages
63
More info:

This is just part of a large block of code that converts different SAP reports into usable Excel reports.

The subtotal column is not always in the same column, though mostly in column A or B
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,064
Office Version
  1. 365
Platform
  1. Windows
More info:

This is just part of a large block of code that converts different SAP reports into usable Excel reports.

The subtotal column is not always in the same column, though mostly in column A or B
Give this a try. It searches columns A, B and C for the word 'Total' to determine what I think you mean by "the subtotal column". The code does not have error checking to see if the word Total occurs in more than 1 of the checked columns. If you need to check more columns, adjust the "For i = 1 to 3" line

Also, I wasn't sure what you wanted to do with the "Grand Total" line. Code may need adjustment to deal with that?

Anyway, give it a try (on a copy of your file).

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Tidy_SubTotals()<br>    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> TotalCol <SPAN style="color:#00007F">As</SPAN> Range<br>    <br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> 3<br>        <SPAN style="color:#00007F">Set</SPAN> TotalCol = Columns(i).Find(What:="Total", LookIn:=xlValues, LookAt:=xlPart)<br>        <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> TotalCol <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>            <SPAN style="color:#00007F">Set</SPAN> TotalCol = Range(Cells(1, i), Cells(Rows.Count, i).End(xlUp))<br>            <SPAN style="color:#00007F">With</SPAN> TotalCol<br>                .AutoFilter Field:=1, Criteria1:="=*Total*"<br>                .Offset(1, 1).Resize(.Rows.Count - 1) _<br>                    .SpecialCells(xlCellTypeVisible).Font.Bold = <SPAN style="color:#00007F">True</SPAN><br>                .AutoFilter<br>                .Replace What:=" Total", Replacement:="", LookAt:=xlPart<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>            <SPAN style="color:#00007F">Set</SPAN> TotalCol = <SPAN style="color:#00007F">Nothing</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> i<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>
 

Watch MrExcel Video

Forum statistics

Threads
1,132,668
Messages
5,654,638
Members
418,147
Latest member
Shnn028

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
Top