Grouping & Outlining (1 Viewer)

[DanG]

Hello,
I have a sheet that I have created an outline on, each group has a total below them. It takes the 1st columns name and adds "totals" to it and then the columns that I get totals for get brought down. There are columns that I do not run totals on and they do not get brought down to the totals column. There are a couple columns I would like to have brough down.

Sample before:
AdvisorNum - FName - LasstName - Miles <----Header row
123 - Bob - Davis - 10
123 - Bob - Davis - 50
Advisor Total - - - 60 <------ Total Row

Sample of desired result:
AdvisorNum - FName - LasstName - Miles <----Header row
123 - Bob - Davis - 10
123 - Bob - Davis - 50
Advisor Total - Bob - Davis - 60 <------ Total Row

So in the end I just want to bring Bob Davis down to the totals row. My thought is to detect the "Advisor Total" in the sheet based on the bold formatting and then do an offset function. But I am not sure.

Any help would be appreciated.

Thank you

 

[DanG]

Got it!

Basically what it does is looks for cells with "totals" in the text and when it finds it, it grabs the cells with the names from ond row above and copies them down. I also have it highlight the row to bold the totals.

It's kind of neat cause it uses offset to look at the values above and bring them down.

Dim myrange As Range
Dim cell As Range
Set myrange = Range("$c$2", Range("d65536").End(xlUp).Offset(1, 0)) '
myrange.Select

    Selection.SpecialCells(xlCellTypeBlanks).Select
    Selection.FormulaR1C1 = "=R[-1]C"

    Range("A2").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=RIGHT($A2,5)=""Total"""
    With Selection.FormatConditions(1).Font
        .Bold = True
        .Italic = False
    End With