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>