Grouping According to IndentLevel

R

ryguy7272

I am trying to arrange elements into groups. My code is below:
Sub Grp()
Dim lngRow As Long

Sheets("Sheet1").Select

For i = 6 To 0 Step -1
For lngRow = Cells(Rows.Count, "B").End(xlUp).Row To 4 Step -1
If Range("B" & lngRow) <> "" And .IndentLevel = (i) Then
Range("B" & lngRow & ":B" & lngRow).Rows.Group
End If
Next lngRow
Next i

End Sub

Basically, I want to start on Row 4, go to the end of the used range, and
group all data according to the ‘Indent’ of each Row. There can be 0 to 6
IndentLevels.
The code fails on this line:
If Range("B" & lngRow) <> "" And .IndentLevel = (i) Then

The message I get says ‘Compile Error: Invalid or unqualified refernce.’
What am I doing wrong?

Thanks,
Ryan---
 
E

Eric G

What object is ".IndentLevel" referring to? Usually, you would have
something like

With ActiveCell
ID = .IndentLevel
End With

or something like that.

HTH,

Eric
 
R

ryguy7272

Yep, I thought of that too. I tried it before I posted the question; didn't
work. Other ideas?

Thanks,
Ryan---
 
E

Eric G

Try tacking "Activesheet." in front of all the Ranges, and you'll still need
to qualify the .IndentLevel with Activecell.IndentLevel or
Cells(1,1).IndentLevel, etc.

Eric
 
R

ryguy7272

Thanks again Eric. Did that work for you? Still can't get this working.
This is what I have now:

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

Maybe the Cells are not being properly selected...
Any more ideas?
 
K

keiji kounoike

I don't understand what you are doing, but this code works without error
in my side. if rows are arranged to IndentLevel, the result would be messed.

Sub Grp()
Dim lngRow As Long
Sheets("Sheet1").Select
For i = 6 To 0 Step -1
For lngRow = Cells(Rows.Count, "B").End(xlUp).Row To 4 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

Keiji
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top