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

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
You must log in or register to reply here.

Similar threads

GimpyHand
  • Question
Duplicate Copy/Paste in VBA issue
Replies
3
Views
331
Joe4
Joe4
B
  • Question
update table
Replies
0
Views
270
BobtBuilder
B
B
  • Question
Macro to Assign "UniqueID's" to Judges tables for a competition
Replies
0
Views
62
BeerMan23
B
M
  • Solved
Finding 1st Blank Cell in a Column, and then Copying data in adjacent columns
2
Replies
12
Views
819
Peter_SSs
Peter_SSs
P
  • Solved
Excel VBA, Vlookup to another sheet in same work book
Replies
6
Views
174
StephenCrump
S

Forum statistics

Threads
1,214,996
Messages
6,122,636
Members
449,092
Latest member
bsb1122

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