?
Solved

VBA for subtotal and highlight the rows

Posted on 2013-11-15
7
Medium Priority
?
1,344 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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 2000 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

800 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