I use the following macro to add some fomatting to my worksheet. I got this from another memeber of EE. It adds subtotals and makes the subtotal line grey shaded... it works great, but I am wondering if it is possible to take it even one step further.
I am not too great at explaing the code, but I will tell you what it does.
Subtotaling by column B, it adds summary totals to columns D,E.F
Now, what I am looking to do is to keep all of that intact, but as part of the macro, hide columns A & B so that the workbook can be printed.
However, I think the real problem is that I lose the subtotal line that appears under column B. So I was wondering if there was a way to move that value over 1 cell?
The screenshot shows after the macro has run. So the additional feature I would like to add would be to hide columns A & B and move the "Dept 1 Total" , 'Dept A Total" over 1 column (under Sales Name, but keeping the Sales Group value.)
Dim wkb As Workbook
Dim wks As Worksheet
Dim rng As Range
Dim r As Range
vflag = 1
Set wkb = ThisWorkbook
Set wks = wkb.ActiveSheet
= True Then
Set rng = wks.Range("A4", wks.Range("F" & wks.Rows.Count).End(xlUp))
rng.subTotal GroupBy:=2, Function:=xlSum, TotalList:=Array(4, 5, 6), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
For Each r In wks.Range("B5", wks.Range("B" & wks.Rows.Count).End(xlUp))
If InStr(r.Value, "Total") <> 0 Then
r.Offset(, 2).Font.Bold = True
r.Offset(, 3).Font.Bold = True
r.Offset(, 4).Font.Bold = True
If r.Value <> "Grand Total" Then
wks.Range("A" & r.Row & ":G" & r.Row).Interior.Color = 12632256 'make subtotal line grey