• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1628
  • Last Modified:

VBA for subtotal and highlight the rows

I have a small spreadsheet That I want to do some subtotals and shading on.  I have enclosed a sample with a tab of Before and a tab of After to illustrate what I want to do using VBA.  Thanks!

VM
Example.xlsx
0
vmccune
Asked:
vmccune
  • 4
  • 3
1 Solution
 
Rob HensonFinance AnalystCommented:
Any particular reasonm you want to use VBA?

There is the Subtotal function that will insert the subtotal rows and do the grouping for you. This is on the Data tab, Outline Group.
 
If you then collapse to total rows and apply the formatting it formats all rows rather than just visible rows.

However, if leave expanded and apply a filter and filter the total column for "Contains Total" when applying the formatting it will only apply to visble cells.

Thanks
Rob H
0
 
vmccuneAuthor Commented:
The actual sheets I will be using this with are much larger and have several tabs.  I really want to automate it with VBA.
0
 
Rob HensonFinance AnalystCommented:
The Subtotal function can handle large data sets on each tab but would have to be automated to cover multiple tabs.

I can write a quck VBA script to apply the subtotal and format to all worksheets.

Be back shortly.
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
vmccuneAuthor Commented:
can you also add the variance and pct calculations?

Thanks.
0
 
Rob HensonFinance AnalystCommented:
VBA Code to do as requested to ALL sheets. Do you know where and how to apply it?

If it shouldn't be applied to all sheets, you will have to specify which ones to omit but should be able to accommodate that.

Sub Apply_Subtotal()

For Each Worksheet In ActiveWorkbook.Worksheets
'Select Sheet
    ShtName = Worksheet.Name
    Sheets(ShtName).Select
    Range("A1").Select
'Apply Subtotals
    Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(2, 3, 4, 5), _
        Replace:=True, PageBreaks:=False, SummaryBelowData:=True
'Apply Filter
            
    Worksheets(ShtName).Range("A1").AutoFilter _
        field:=1, _
        Criteria1:="=*total*", _
        VisibleDropDown:=True
    
'Apply Format
    Selection.CurrentRegion.Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent5
        .TintAndShade = 0.399975585192419
        .PatternTintAndShade = 0
    End With
    
'Remove Format from Header Row
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    With Selection.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    ActiveSheet.ShowAllData
    Selection.AutoFilter
    Range("A1").Select

Next Worksheet

End Sub

Open in new window


If the formatting isn't right adjust the values in the "Apply Format section". To get the correct settings, start the VBA Recorder and apply the formatting required. Then stop the recorder and copy the result into this script.

Thanks
Rob H
0
 
Rob HensonFinance AnalystCommented:
hadn't seen the formula in column D request before submitting, add this extra script before Line 40 "Next Worksheet"

'Add formula to column D
    Range("D2").Select
    Range(Selection, Selection.End(xlDown)).Select
    For Each Cell In Selection
        ActiveCell.FormulaR1C1 = "=(RC[-2]/RC[-1])-1"
    Next Cell
    Range("D2").Select

Open in new window


Thanks
Rob H
0
 
vmccuneAuthor Commented:
The Percent change in the subtotal is summing the percent.  It should be the same calculation.  The variance is fine as a calculation.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now