Looping Question

[ryguy7272]

This code will go through the used range and do the grouping for the cells
with IndentLevels that are even. However, for IndentLevels that are odd,
there is no grouping. I tried a double-loop, like For i…Next i and For
j…Next j, but that didn’t work.


Sub Grp()
Dim lngRow As Long
Sheets("Sheet1").Select
For i = 12 To 0 Step -2
For lngRow = Cells(Rows.Count, "B").End(xlUp).Row To 2 Step -1
If Range("B" & lngRow) <> "" And Range("B" & lngRow).IndentLevel =
(i) Then
Range("B" & lngRow).Rows.Group
End If
Next lngRow
Next i
End Sub

What do I need to do to make this group both even Indents and odd Indents?

Thanks,
Ryan---

P.S., sorry for that other post...not sure how THAT happened...

 

[ker_01]

For i = 12 To 0 Step -2

When counting down (12 to zero) the "Step" tells it what increment to count
in. I didn't review your code in detail, but try changing this line to:

For i = 12 To 0 Step -1

and see if that helps.

Best,
Keith

 

[ryguy7272]

Forgot to mention it, but I tried that; didn't work.
Anything else? Porbably simple, I'm just not seeing it.

Thanks,
Ryan---

 

[ker_01]

My mistake on the i loop- since you are using that to compare indent level,
If you use Step -2, you will only be capturing indent levels 12, 10, 8, 6, 4,
2, 0 but not skipping every other row. Your step -1 on lngRow looks correct
to me to catch each row.

Without seeing the worksheet to disconfirm, I have to wonder if the even
rows all have indents that are 'odd' (1,3,5,7,9,11) or have a value of
13/14/15, since those indent levels are also ignored in your loop...But you
said you tried step -1 on the i loop, so that probably isn't it.

I ran a short test to see if grouping would mess up the row count, but
didn't have any problems with it catching each row and grouping them
appropriately on my machine.

Hopefully one of the many people in this group smarter than me will post
other ideas about what to check. In the meantime, I'd just suggest walking
through the code (F8) and see what it does when it hits even rows (lngRow
values). I tried several other variations of the code to check some of my
own assumptions, but didn't see anything unusual.

Can you post a sample of what you have in column B, maybe rows 2 through 10
(since that should be a sufficient sample to test), and include the level of
indent for each of those cells?

Sorry I don't have a more direct answer!

Best,
Keith

 

[ryguy7272]

Thanks Keith!! This is the structure of things...

Cell B2, Indent = 0
Cell B3, Indent = 1
Cell B4, Indent = 2
Cell B5, Indent = 3
Cell B6, Indent = 4
Cell B7, Indent = 5
Cells B8:B30, Indent = 6
Cell B31, Indent = 5
Cells B32:B34, Indent = 6
Cell B55, Indent = 5
Cells B56:B78, Indent = 6
....this pattern continues for a bit...
Cell B323, indent = 4
So anyway, there must be something wrong with the counter. I tried this:
For i = 6 To 0 Step -1

When the code fires, everything is grouped into one massive chunk, which
contains 1498 Rows. This is not right, because of the indents described
above. If I change the counter to this:
For i = 12 To 0 Step -2

When the code fires I have a two levels of groupings. When I hit the 1 (in
the upper left hand corner), everything is grouped and when I click the 2,
everything is ungrouped. When I click the 1, Cell B3 is displayed and it has
an Indent of 1, Cell B5 is displayed and it has an Indent of 3, Cell B7 is
displayed and it has an Indent of 5, Cell B31 is displayed...Cell B55 is
displayed...and so on and so forth.

I guess I need a second counter to pick up the Indents with even numbers,
but I am not sure how to set up this counter. Any ideas?

Thanks,
Ryan---

 

[Jacob Skaria]

Hi Ryan

I have just modified your code to group the rows with an even indent level;
but I am not sure whether this is what you are looking for..(coz I am bit
confused with your first loop). Try and feedback so as to be clear...

Sub Grp()
Dim lngRow As Long
Sheets("Sheet3").Select
For lngRow = Cells(Rows.Count, "B").End(xlUp).Row To 2 Step -1
If Range("B" & lngRow) <> "" And _
Range("B" & lngRow).IndentLevel > 0 And _
Range("B" & lngRow).IndentLevel Mod 2 = 0 Then
Range("B" & lngRow).Rows.Group
End If
Next lngRow
End Sub



If this post helps click Yes

 

[ryguy7272]

Using the Mod operator is a great idea Jacob! That’s one that I didn’t think
of until you put it out there. Unfortunately, something is still wrong here.
If I use Mod = 0, I get the odd IndentLines and if I use Mod = 1, I get the
even IndentLines. The code is definitely doing what I tell it do, but it is
still not doing what I need it to do. I want to group the odds AND evens. I
think the counter is fine and I think the loop is fine; I just need to do a
second loop, I think.

For instance, I have one cell with IndentLines = 2, which is on row 4. The
next time I have a cell with IndentLines = 2 is on row 1508. I would like to
see everything rolled up, or grouped, based on these LineIndents. Similarly,
row IndentLines = 3 on row 5. The next cell with IndentLines = 3 is row
1500. I would like to see everything between these rows grouped. In the
same fashion, IndentLines = 4 on row 6 and The next time I have a cell with
IndentLines = 4 is on row 323. I’d like to see everything between these
groups grouped.

Thanks again!
Ryan---

 

[Jacob Skaria]

Hi Ryan

OK. Now it is a bit more clear from your explanation of what you are trying
to acheive. You dont need a second loop anyway. I have modified the
code...Try and feedback. This is not been tested in full..might need some
modifications..but still the logic would be same.....

Sub Macro()

Dim intIndent As Integer
Dim intTemp As Integer
Dim lngRow As Long
Dim arrIndent() As Long

ReDim arrIndent(6)
For lngRow = 1 To Cells(Rows.Count, "B").End(xlUp).Row
If Range("B" & lngRow).IndentLevel > 0 And _
Range("B" & lngRow).IndentLevel <> intIndent Then
intIndent = Range("B" & lngRow).IndentLevel
If arrIndent(intIndent) <> Empty Then
Rows(arrIndent(intIndent) + 1 & ":" & lngRow - 1).Group
For intTemp = intIndent To 6: arrIndent(intTemp) = Empty: Next
Else
arrIndent(intIndent) = lngRow
End If
End If
Next lngRow


End Sub

If this post helps click Yes

 

[Jacob Skaria]

Few corrections...

Sub Macro()

Dim intIndent As Integer
Dim intTemp As Integer
Dim lngRow As Long
Dim arrIndent() As Long

ReDim arrIndent(6)
For lngRow = 1 To Cells(Rows.Count, "B").End(xlUp).Row
If Range("B" & lngRow).IndentLevel > 0 And _
Range("B" & lngRow).IndentLevel <> intIndent Then
intIndent = Range("B" & lngRow).IndentLevel
If arrIndent(intIndent) <> 0 Then
Rows(arrIndent(intIndent) + 1 & ":" & lngRow - 1).Group
For intTemp = intIndent To 6: arrIndent(intTemp) = 0: Next
Else
arrIndent(intIndent) = lngRow
End If
End If
Next lngRow


End Sub

 

[ryguy7272]

Awesome!! Thanks so much!!
Ryan--