Solved

VBA for subtotal and highlight the rows

Posted on 2013-11-15
7
1,193 Views
Last Modified: 2013-11-20
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
Comment
Question by:vmccune
  • 4
  • 3
7 Comments
 
LVL 31

Expert Comment

by:Rob Henson
Comment Utility
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
 

Author Comment

by:vmccune
Comment Utility
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
 
LVL 31

Expert Comment

by:Rob Henson
Comment Utility
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

Author Comment

by:vmccune
Comment Utility
can you also add the variance and pct calculations?

Thanks.
0
 
LVL 31

Expert Comment

by:Rob Henson
Comment Utility
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
 
LVL 31

Accepted Solution

by:
Rob Henson earned 500 total points
Comment Utility
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
 

Author Comment

by:vmccune
Comment Utility
The Percent change in the subtotal is summing the percent.  It should be the same calculation.  The variance is fine as a calculation.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now