Solved

VBA for subtotal and highlight the rows

Posted on 2013-11-15
7
1,199 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 32

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 32

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

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

Thanks.
0
 
LVL 32

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 32

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
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 view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

910 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

23 Experts available now in Live!

Get 1:1 Help Now