Hello again,

I have an "outline" issue:

Actually Rows should be GROUPED. No totals to be made ..just grouping
some rows together.

Situation:

-The grouping should be based on with condition in column B:
ie: Is the font of this cell BOLD?
-Evaluation of this condition always starts on row 7.


So, I might have 1000 rows.
If cell B19 = Bold then Group row 7 till 18
If next bold cell = B35 then Group row 20 till 34

etc etc

( If there are 2 adjacent B-cells in BOLD, then basically a 2nd level
grouping should happen.
In the example: If B36 = Bold then 2nd level Grouping should happen:
Grouping Row7-Row35 )

No idea on how to knit this together,
Looking forward to your ideas!
Sige

This seemed to work.

Option Explicit
Sub testme2()

Dim wks As Worksheet
Dim NextBoldCell As Range
Dim CurBoldCell As Range
Dim RowDiff As Long
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long

Set wks = Worksheets("Sheet1")
wks.Rows.ClearOutline 'clear previous testing

With wks
FirstRow = 7
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row + 1
'end with a bold cell
With .Cells(LastRow, "B")
.Value = "DUMMY"
.Font.Bold = True
End With

Set CurBoldCell = .Cells(FirstRow, "B")

For iRow = FirstRow + 1 To LastRow
If IsBold(.Cells(iRow, "B")) Then
Set NextBoldCell = .Cells(iRow, "B")
RowDiff = NextBoldCell.Row - CurBoldCell.Row
If RowDiff 2 Then
'.Rows(NextBoldCell.Row + 1).Resize(RowDiff - 1).Select
.Rows(CurBoldCell.Row + 1).Resize(RowDiff - 1).Group
End If
If IsBold(NextBoldCell.Offset(1, 0)) Then
'don't change starting point
Else
Set CurBoldCell = NextBoldCell
End If
End If
Next iRow

With .Cells(.Rows.Count, "B").End(xlUp)
.ClearContents
.Font.Bold = False
End With
End With

End Sub
Function IsBold(myCell As Range) As Boolean
IsBold = False
If myCell(1).Font.Bold = True Then
IsBold = True
End If
End Function


Sige wrote:

Hello again,

I have an "outline" issue:

Actually Rows should be GROUPED. No totals to be made ..just grouping
some rows together.

Situation:

-The grouping should be based on with condition in column B:
ie: Is the font of this cell BOLD?
-Evaluation of this condition always starts on row 7.

So, I might have 1000 rows.
If cell B19 = Bold then Group row 7 till 18
If next bold cell = B35 then Group row 20 till 34

etc etc

( If there are 2 adjacent B-cells in BOLD, then basically a 2nd level
grouping should happen.
In the example: If B36 = Bold then 2nd level Grouping should happen:
Grouping Row7-Row35 )

No idea on how to knit this together,
Looking forward to your ideas!
Sige

--

Dave Peterson

Hi Dave,

Very very nice!!! :o))

It does not "outline" everywhere it is supposed to ...

-The first outline he "forgets"
eg.
Row7:Row9 =not bold
Row 10= bold

-If you have just 1row Not Bold and the next Bold = he misses out the
outline
like Row12 =not bold
row13bold =bold
=this outline he does not set

The second level-outlining gets a bit tangled. It should capture all
first level outlinings above this 2 double cell:

Row7:Row10=Notbold
Row11= bold
=outline1level_a

Row12:Row13=notbold
Row14=Bold
=outline1level_b
row15:row15=bold
=outline2level capturing row7:row14

Hope it makes sense?
Sige

Try changing:

If RowDiff 2 Then
to
If RowDiff 1 Then

I'm confused about what the other stuff means.

Maybe you could just give some test data starting with B7:

Bold
not
bold
not
not

(Not too much--just enough to find the trouble spots.)

Sige wrote:

Hi Dave,

Very very nice!!! :o))

It does not "outline" everywhere it is supposed to ...

-The first outline he "forgets"
eg.
Row7:Row9 =not bold
Row 10= bold

-If you have just 1row Not Bold and the next Bold = he misses out the
outline
like Row12 =not bold
row13bold =bold
=this outline he does not set

The second level-outlining gets a bit tangled. It should capture all
first level outlinings above this 2 double cell:

Row7:Row10=Notbold
Row11= bold
=outline1level_a

Row12:Row13=notbold
Row14=Bold
=outline1level_b
row15:row15=bold
=outline2level capturing row7:row14

Hope it makes sense?
Sige

--

Dave Peterson

Hi Dave,
Sorry for the confusions I am creating...
Here we go:

B7:NOT
B8:NOT
B9:BOLD =outline level1 (grouping row7-8)
B10:NOT
B11:NOT
B12:NOT
B13:BOLD =outline level1 (grouping row10-12)
B14:NOT
B15:BOLD =outline level1 (grouping row14)
B16:NOT
B17:NOT
B18:BOLD =outline level1 (grouping row 16-17)
B19:BOLD = outline level2 (grouping row 7-18)

B20:NOT
B21:BOLD =outline level1 (grouping row20)
B22:NOT
B23:NOT
B24:BOLD = outline level1 (grouping row22-23)
B25:BOLD = outline level2 (grouping row20-24),

Potentially:
B26:BOLD = Outline level3 (grouping row 7-25)

etc etc


Really Hope this makes it clearer Bob,
Sige

Dave Peterson wrote:
Try changing:

If RowDiff 2 Then
to
If RowDiff 1 Then

I'm confused about what the other stuff means.

Maybe you could just give some test data starting with B7:

Bold
not
bold
not
not

(Not too much--just enough to find the trouble spots.)

Sige wrote:

Hi Dave,

Very very nice!!! :o))

It does not "outline" everywhere it is supposed to ...

-The first outline he "forgets"
eg.
Row7:Row9 =not bold
Row 10= bold

-If you have just 1row Not Bold and the next Bold = he misses out the
outline
like Row12 =not bold
row13bold =bold
=this outline he does not set

The second level-outlining gets a bit tangled. It should capture all
first level outlinings above this 2 double cell:

Row7:Row10=Notbold
Row11= bold
=outline1level_a

Row12:Row13=notbold
Row14=Bold
=outline1level_b
row15:row15=bold
=outline2level capturing row7:row14

Hope it makes sense?
Sige

--

Dave Peterson

I kind of see what you're doing, but I don't understand the rules.

B7:NOT
B8:NOT
1 B9:BOLD =outline level1 (grouping row7-8)
B10:NOT
B11:NOT
B12:NOT
2 B13:BOLD =outline level1 (grouping row10-12)
B14:NOT
3 B15:BOLD =outline level1 (grouping row14)
B16:NOT
B17:NOT
4 B18:BOLD =outline level1 (grouping row 16-17)
1 B19:BOLD = outline level2 (grouping row 7-18)
B20:NOT
5 B21:BOLD =outline level1 (grouping row20)
B22:NOT
B23:NOT
6 B24:BOLD = outline level1 (grouping row22-23)
5 B25:BOLD = outline level2 (grouping row20-24)
1 B26:BOLD = Outline level3 (grouping row 7-25)

The numbers were my feeble attempt to "group" the rows.

1 is a new group, 2 is a new group, 3 a new group, 4 a new group.

But B19 is grouped with top of group 1

5 & 6 (b21 & b24) are new groups
But 5 (b25) is grouped with 5

Why would B26 jump all the way back to 1 (and not 4)?

I was thinking that maybe you were using grouping kind of like parentheses.

(((a+b)+c)+d)
to get the grouping, but that doesn't fit.

If I were doing this, I think I'd take another approach.

Try to mark the rows with the line they should be grouped with.

7 B7:NOT
B8:NOT
7 B9:BOLD =outline level1 (grouping row7-8)
10 B10:NOT
B11:NOT
B12:NOT
10 B13:BOLD =outline level1 (grouping row10-12)
14 B14:NOT
14 B15:BOLD =outline level1 (grouping row14)
16 B16:NOT
B17:NOT
16 B18:BOLD =outline level1 (grouping row 16-17)
7 B19:BOLD = outline level2 (grouping row 7-18)
20 B20:NOT
20 B21:BOLD =outline level1 (grouping row20)
22 B22:NOT
B23:NOT
22 B24:BOLD = outline level1 (grouping row22-23)
20 B25:BOLD = outline level2 (grouping row20-24),
7 B26:BOLD = Outline level3 (grouping row 7-25)

Then you could look through this list.

If the number appears for the first time, don't do anything.

If it's the second (or third or ...), then find the first matching number in the
list and group them.

I'm not sure if this possible. But if you can do it manually, then maybe it can
be turned into a formula and that formula could be applied in code and then the
grouping part is pretty straight forward.


And I have one more question. What does this mean?

"Really Hope this makes it clearer Bob,"

<vbg



Sige wrote:

Hi Dave,
Sorry for the confusions I am creating...
Here we go:

B7:NOT
B8:NOT
B9:BOLD =outline level1 (grouping row7-8)
B10:NOT
B11:NOT
B12:NOT
B13:BOLD =outline level1 (grouping row10-12)
B14:NOT
B15:BOLD =outline level1 (grouping row14)
B16:NOT
B17:NOT
B18:BOLD =outline level1 (grouping row 16-17)
B19:BOLD = outline level2 (grouping row 7-18)

B20:NOT
B21:BOLD =outline level1 (grouping row20)
B22:NOT
B23:NOT
B24:BOLD = outline level1 (grouping row22-23)
B25:BOLD = outline level2 (grouping row20-24),

Potentially:
B26:BOLD = Outline level3 (grouping row 7-25)

etc etc

Really Hope this makes it clearer Bob,
Sige

Dave Peterson wrote:
Try changing:

If RowDiff 2 Then
to
If RowDiff 1 Then

I'm confused about what the other stuff means.

Maybe you could just give some test data starting with B7:

Bold
not
bold
not
not

(Not too much--just enough to find the trouble spots.)

Sige wrote:

Hi Dave,

Very very nice!!! :o))

It does not "outline" everywhere it is supposed to ...

-The first outline he "forgets"
eg.
Row7:Row9 =not bold
Row 10= bold

-If you have just 1row Not Bold and the next Bold = he misses out the
outline
like Row12 =not bold
row13bold =bold
=this outline he does not set

The second level-outlining gets a bit tangled. It should capture all
first level outlinings above this 2 double cell:

Row7:Row10=Notbold
Row11= bold
=outline1level_a

Row12:Row13=notbold
Row14=Bold
=outline1level_b
row15:row15=bold
=outline2level capturing row7:row14

Hope it makes sense?
Sige

--

Dave Peterson

--

Dave Peterson