Help generating outline groupings (the little +/- things) with vba

T

Tcm43

New Member
Joined
Jun 28, 2013
Messages
14
I have been searching near and far for vba code that can let me do this. I found someones code but I couldn't get it to work :( Anyone come across something like this before?

Below is the code I came across:
Code: 
<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: inherit;">[COLOR=#00008B]Public[/COLOR][COLOR=#000000] [/COLOR][COLOR=#00008B]Sub[/COLOR][COLOR=#000000] GroupCells[/COLOR][COLOR=#000000]()[/COLOR][COLOR=#000000]
    [/COLOR][COLOR=#00008B]Dim[/COLOR][COLOR=#000000] myRange [/COLOR][COLOR=#00008B]As[/COLOR][COLOR=#000000] Range
    [/COLOR][COLOR=#00008B]Dim[/COLOR][COLOR=#000000] rowCount [/COLOR][COLOR=#00008B]As[/COLOR][COLOR=#000000] [/COLOR][COLOR=#00008B]Integer[/COLOR][COLOR=#000000],[/COLOR][COLOR=#000000] currentRow [/COLOR][COLOR=#00008B]As[/COLOR][COLOR=#000000] [/COLOR][COLOR=#00008B]Integer[/COLOR][COLOR=#000000]
    [/COLOR][COLOR=#00008B]Dim[/COLOR][COLOR=#000000] firstBlankRow [/COLOR][COLOR=#00008B]As[/COLOR][COLOR=#000000] [/COLOR][COLOR=#00008B]Integer[/COLOR][COLOR=#000000],[/COLOR][COLOR=#000000] lastBlankRow [/COLOR][COLOR=#00008B]As[/COLOR][COLOR=#000000] [/COLOR][COLOR=#00008B]Integer[/COLOR][COLOR=#000000]
    [/COLOR][COLOR=#00008B]Dim[/COLOR][COLOR=#000000] currentRowValue [/COLOR][COLOR=#00008B]As[/COLOR][COLOR=#000000] [/COLOR][COLOR=#00008B]String[/COLOR][COLOR=#000000]
    [/COLOR][COLOR=#00008B]Dim[/COLOR][COLOR=#000000] neighborColumnValue [/COLOR][COLOR=#00008B]As[/COLOR][COLOR=#000000] [/COLOR][COLOR=#00008B]String[/COLOR][COLOR=#000000]

    [/COLOR][COLOR=#808080]'select range based on given named range[/COLOR][COLOR=#000000]
    [/COLOR][COLOR=#00008B]Set[/COLOR][COLOR=#000000] myRange [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#000000] Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#800000]"rngList"[/COLOR][COLOR=#000000])[/COLOR][COLOR=#000000]
    rowCount [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#000000] Cells[/COLOR][COLOR=#000000]([/COLOR][COLOR=#000000]Rows[/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]Count[/COLOR][COLOR=#000000],[/COLOR][COLOR=#000000] myRange[/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]Column[/COLOR][COLOR=#000000]).[/COLOR][COLOR=#00008B]End[/COLOR][COLOR=#000000]([/COLOR][COLOR=#000000]xlUp[/COLOR][COLOR=#000000]).[/COLOR][COLOR=#000000]Row

    firstBlankRow [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#000000] [/COLOR][COLOR=#800000]0[/COLOR][COLOR=#000000]
    lastBlankRow [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#000000] [/COLOR][COLOR=#800000]0[/COLOR][COLOR=#000000]
    [/COLOR][COLOR=#808080]'for every row in the range[/COLOR][COLOR=#000000]
    [/COLOR][COLOR=#00008B]For[/COLOR][COLOR=#000000] currentRow [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#000000] [/COLOR][COLOR=#800000]1[/COLOR][COLOR=#000000] [/COLOR][COLOR=#00008B]To[/COLOR][COLOR=#000000] rowCount
        currentRowValue [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#000000] Cells[/COLOR][COLOR=#000000]([/COLOR][COLOR=#000000]currentRow[/COLOR][COLOR=#000000],[/COLOR][COLOR=#000000] myRange[/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]Column[/COLOR][COLOR=#000000]).[/COLOR][COLOR=#000000]Value
        neighborColumnValue [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#000000] Cells[/COLOR][COLOR=#000000]([/COLOR][COLOR=#000000]currentRow[/COLOR][COLOR=#000000],[/COLOR][COLOR=#000000] myRange[/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]Column [/COLOR][COLOR=#000000]-[/COLOR][COLOR=#000000] [/COLOR][COLOR=#800000]1[/COLOR][COLOR=#000000]).[/COLOR][COLOR=#000000]Value

        [/COLOR][COLOR=#00008B]If[/COLOR][COLOR=#000000] [/COLOR][COLOR=#000000]([/COLOR][COLOR=#000000]IsEmpty[/COLOR][COLOR=#000000]([/COLOR][COLOR=#000000]currentRowValue[/COLOR][COLOR=#000000])[/COLOR][COLOR=#000000] [/COLOR][COLOR=#00008B]Or[/COLOR][COLOR=#000000] currentRowValue [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#000000] [/COLOR][COLOR=#800000]""[/COLOR][COLOR=#000000])[/COLOR][COLOR=#000000] [/COLOR][COLOR=#00008B]Then[/COLOR][COLOR=#000000]
            [/COLOR][COLOR=#808080]'if cell is blank and firstBlankRow hasn't been assigned yet[/COLOR][COLOR=#000000]
            [/COLOR][COLOR=#00008B]If[/COLOR][COLOR=#000000] firstBlankRow [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#000000] [/COLOR][COLOR=#800000]0[/COLOR][COLOR=#000000] [/COLOR][COLOR=#00008B]Then[/COLOR][COLOR=#000000]
                firstBlankRow [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#000000] currentRow
            [/COLOR][COLOR=#00008B]End[/COLOR][COLOR=#000000] [/COLOR][COLOR=#00008B]If[/COLOR][COLOR=#000000]
        [/COLOR][COLOR=#00008B]ElseIf[/COLOR][COLOR=#000000] [/COLOR][COLOR=#00008B]Not[/COLOR][COLOR=#000000] [/COLOR][COLOR=#000000]([/COLOR][COLOR=#000000]IsEmpty[/COLOR][COLOR=#000000]([/COLOR][COLOR=#000000]currentRowValue[/COLOR][COLOR=#000000])[/COLOR][COLOR=#000000] [/COLOR][COLOR=#00008B]Or[/COLOR][COLOR=#000000] currentRowValue [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#000000] [/COLOR][COLOR=#800000]""[/COLOR][COLOR=#000000])[/COLOR][COLOR=#000000] [/COLOR][COLOR=#00008B]Then[/COLOR][COLOR=#000000]
            [/COLOR][COLOR=#808080]'if the cell is not blank and its neighbor's (to the left) value is 0,[/COLOR][COLOR=#000000]
            [/COLOR][COLOR=#808080]'and firstBlankRow hasn't been assigned, then this is the firstBlankRow[/COLOR][COLOR=#000000]
            [/COLOR][COLOR=#808080]'to consider for grouping[/COLOR][COLOR=#000000]
            [/COLOR][COLOR=#00008B]If[/COLOR][COLOR=#000000] neighborColumnValue [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#000000] [/COLOR][COLOR=#800000]0[/COLOR][COLOR=#000000] [/COLOR][COLOR=#00008B]And[/COLOR][COLOR=#000000] firstBlankRow [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#000000] [/COLOR][COLOR=#800000]0[/COLOR][COLOR=#000000] [/COLOR][COLOR=#00008B]Then[/COLOR][COLOR=#000000]
                firstBlankRow [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#000000] currentRow
            [/COLOR][COLOR=#00008B]ElseIf[/COLOR][COLOR=#000000] neighborColumnValue [/COLOR][COLOR=#000000]<>[/COLOR][COLOR=#000000] [/COLOR][COLOR=#800000]0[/COLOR][COLOR=#000000] [/COLOR][COLOR=#00008B]And[/COLOR][COLOR=#000000] firstBlankRow [/COLOR][COLOR=#000000]<>[/COLOR][COLOR=#000000] [/COLOR][COLOR=#800000]0[/COLOR][COLOR=#000000] [/COLOR][COLOR=#00008B]Then[/COLOR][COLOR=#000000]
                [/COLOR][COLOR=#808080]'if firstBlankRow is assigned and this row has a value with a neighbor[/COLOR][COLOR=#000000]
                [/COLOR][COLOR=#808080]'who isn't 0, then the cell one row above this one is to be considered[/COLOR][COLOR=#000000]
                [/COLOR][COLOR=#808080]'the lastBlankRow to include in the grouping[/COLOR][COLOR=#000000]
                lastBlankRow [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#000000] currentRow [/COLOR][COLOR=#000000]-[/COLOR][COLOR=#000000] [/COLOR][COLOR=#800000]1[/COLOR][COLOR=#000000]
            [/COLOR][COLOR=#00008B]End[/COLOR][COLOR=#000000] [/COLOR][COLOR=#00008B]If[/COLOR][COLOR=#000000]
        [/COLOR][COLOR=#00008B]End[/COLOR][COLOR=#000000] [/COLOR][COLOR=#00008B]If[/COLOR][COLOR=#000000]

        [/COLOR][COLOR=#808080]'if first AND last blank rows have been assigned, then create a group[/COLOR][COLOR=#000000]
        [/COLOR][COLOR=#808080]'then reset the first/lastBlankRow values to 0 and begin searching for next[/COLOR][COLOR=#000000]
        [/COLOR][COLOR=#808080]'grouping[/COLOR][COLOR=#000000]
        [/COLOR][COLOR=#00008B]If[/COLOR][COLOR=#000000] firstBlankRow [/COLOR][COLOR=#000000]<>[/COLOR][COLOR=#000000] [/COLOR][COLOR=#800000]0[/COLOR][COLOR=#000000] [/COLOR][COLOR=#00008B]And[/COLOR][COLOR=#000000] lastBlankRow [/COLOR][COLOR=#000000]<>[/COLOR][COLOR=#000000] [/COLOR][COLOR=#800000]0[/COLOR][COLOR=#000000] [/COLOR][COLOR=#00008B]Then[/COLOR][COLOR=#000000]
            Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#000000]Cells[/COLOR][COLOR=#000000]([/COLOR][COLOR=#000000]firstBlankRow[/COLOR][COLOR=#000000],[/COLOR][COLOR=#000000] myRange[/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]Column[/COLOR][COLOR=#000000]),[/COLOR][COLOR=#000000] Cells[/COLOR][COLOR=#000000]([/COLOR][COLOR=#000000]lastBlankRow[/COLOR][COLOR=#000000],[/COLOR][COLOR=#000000] myRange[/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]Column[/COLOR][COLOR=#000000])).[/COLOR][COLOR=#000000]EntireRow[/COLOR][COLOR=#000000].[/COLOR][COLOR=#00008B]Select[/COLOR][COLOR=#000000]
            Selection[/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]Group
            firstBlankRow [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#000000] [/COLOR][COLOR=#800000]0[/COLOR][COLOR=#000000]
            lastBlankRow [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#000000] [/COLOR][COLOR=#800000]0[/COLOR][COLOR=#000000]
        [/COLOR][COLOR=#00008B]End[/COLOR][COLOR=#000000] [/COLOR][COLOR=#00008B]If[/COLOR][COLOR=#000000]
    [/COLOR][COLOR=#00008B]Next[/COLOR][COLOR=#000000]
[/COLOR][COLOR=#00008B]End[/COLOR][COLOR=#000000] [/COLOR][COLOR=#00008B]Sub[/COLOR]</code>

Much thanks in advance.
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
You must log in or register to reply here.

Similar threads

M
  • Solved
Finding 1st Blank Cell in a Column, and then Copying data in adjacent columns
2
Replies
12
Views
655
Peter_SSs
Peter_SSs
D
  • Question
Quick VBA Macro to group rows based on cell values - Outline Style
Replies
0
Views
280
Dedonovan
D
E
  • Solved
Trying to group rows with same values in VBA
2
Replies
13
Views
1K
Joe4
Joe4
D
  • Solved
Sequential Numbering of New Records using VBA
2
Replies
10
Views
877
dmt32
D
T
  • Question
[VBA] Compare Two Sheets, Highlight Differences, But Consider Add/Remove Rows
Replies
0
Views
739
TimidBee
T

Forum statistics

Threads
1,196,368
Messages
6,014,868
Members
441,851
Latest member
Cheeseballs99

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top