Solved

VBA for subtotal and highlight the rows

Posted on 2013-11-15
7
1,302 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 33

Expert Comment

by:Rob Henson
ID: 39650993
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
ID: 39651007
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 33

Expert Comment

by:Rob Henson
ID: 39651042
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:vmccune
ID: 39651094
can you also add the variance and pct calculations?

Thanks.
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 39651118
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 33

Accepted Solution

by:
Rob Henson earned 500 total points
ID: 39651137
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
ID: 39662777
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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

717 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