Bold lines that contains 'Total'

[fddekker]

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!!

 

[Tom Urtis]

http://www.mrexcel.com/forum/showthread.php?t=231884

Except that to delete the "Total" values will require VBA so post back if you really need to do that, as the CF will make those subtotal rows stand out.

 

[Peter_SSs]

The whole process could be done pretty quickly manually - do you really need code?

 

[fddekker]

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]

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>