PDA

View Full Version : Creating an outline based on data in column:



corbantd
08-16-2012, 07:47 AM
I have a column that contains references I'd like to use to create an outline.

I figured out how to write a formula that gives me either a 0 or the first and last row of the range I'd liked grouped, so the column reads as follows:

0
4:430
5:5
0
7:165
0
0
0
11:21
0
0

etc.

What I can't get right is a code that goes down that column and groups the listed rows into an outline.

Any help would be fantastic!

Thank you!

Corban

CatDaddy
08-16-2012, 09:32 AM
what do you mean by an outline?

corbantd
08-16-2012, 12:00 PM
when I say "outline" I mean that I'd like it to "group" the rows so that I can expand and collapse them using the + and - on the right (the most basic form of the VBA is << Rows("4:8").Group >>, I think) and those would be nested inside each other.

So what it would need to do is start on a cell (call it a1) and see if the value is 0. If it's not, then the cell will have a range inside ("4:8") that I want to be grouped. Then I want it to do that the next cell in the column and every cell until the cell it moves to is empty.

Thank you!

CatDaddy
08-16-2012, 01:54 PM
Sub alex()
Dim cell As Range
Dim lr, r As Long
'ASSUMING YOUR DATA IS IN COL A AND GOES
'TO THE BOTTOM OF YOUR RANGE
ActiveWorkbook.Sheets(1).Activate
lr = Range("A" & Rows.Count).End(xlUp).Row
For Each cell In Range("A2:A" & lr)
If cell.Value <> 0 And InStr(cell.Text, ":") Then Rows(cell.Text).Group
Next cell
End Sub

corbantd
08-16-2012, 03:06 PM
OUTSTANDING!

Thank you!

:beerchug:

CatDaddy
08-17-2012, 08:34 AM
mark it solved :)

alexpantex
11-10-2015, 04:25 AM
Hi, I know it's old thread, but I face similar problem, just instead this range I need to group rows based on a value in cell, so if in A1 i have number 1, than it dont need grouping, if it's 2 than to group it once (first level), if it's 3 to group it twice. Is it possible to modify this macro to perform this action?

Thank you in advance