Treeview macro (2k)

[officespacer]

Ok this macro I can't find an example of, but what I want to do is pretty simple (i think). Hans helped me fix the macro at the bottom of this post a few minutes ago - works perfectly. With this macro I want to insert another section that sets up a tree view of a list of items I have output from some program. It outputs things in this format:

item
.item
..item
..item
...item
...item
...item
..item
..item
.item
..item
...item
.item

I guess it isn't necessary to put it in the SAME sub, but it would be nice to only have to run 1 macro. Anyway, as you can see the items above follow a number of periods that act as the level of the item next to it. So it is a tree already, its just tough to see it on the sheet in that format and differentiate the items. I'd like to make macro that moves the item X cells to the right, where X = the number of periods in front of the item (X can be = to zero since there may be no periods in front of an item (top level)). Each item and it's periods are in the same cell.

Public Sub Treeview_and_DelBlankRows()
Dim I As Long
Dim J As Long
Dim f As Boolean

For I = Range("A65536").End(xlUp).Row - 1 To 1 Step -1
f = True
For J = 1 To Cells(I, 255).End(xlToLeft).Column
If Trim(Cells(I, J)) <> "" Then
f = False
End If
Next J
If f = True Then
Rows(I).Delete
End If
Next I

End Sub


I'm thinking as it goes through the outter loop, I can put a line in that moves cells to the right, but I don't know how to look for up to 8 possible periods.

[HansV]

Since "delete blank lines" and "create tree" do quite different things, I would create a separate macro for each. You can use a third macro to call them both:

Public Sub Treeview_and_DelBlankRows()
DelBlankRows
CreateTree
End Sub

Here is code to create the "treeview". It assumes that column A contains the items.

Sub CreateTree()
Dim i As Long
Dim j As Long
Dim n As Long
For i = 1 To Range("A65536").End(xlUp).Row
n = 0
For j = 1 To Len(Cells(i, 1))
If Mid(Cells(i, 1), j, 1) = " " Then
n = n + 1
Else
Exit For
End If
Next j
If n > 0 Then
Cells(i, n + 1) = Trim(Cells(i, 1))
Cells(i, 1).ClearContents
End If
Next i
End Sub

[officespacer]

I'm a little confused, where does it search for periods?

and the cell is moved when? is it at:

Cells(i, n + 1) = Trim(Cells(i, 1))
Cells(i, 1).ClearContents

just trying to understand it so i don't have to bother you guys over this stuff again <img src=/S/blackteeth.gif border=0 alt=blackteeth width=20 height=20>

[HansV]

Sorry, my fault. I didn't read your question carefully and searched for spaces instead of periods. Here is the (hopefully) correct version:

Sub CreateTree()
Dim i As Long
Dim j As Long
Dim n As Long
For i = 1 To Range("A65536").End(xlUp).Row
n = 0
For j = 1 To Len(Cells(i, 1))
If Mid(Cells(i, 1), j, 1) = "." Then
n = n + 1
Else
Exit For
End If
Next j
If n > 0 Then
Cells(i, n + 1) = Mid(Cells(i, 1), n + 1)
Cells(i, 1).ClearContents
End If
Next i
End Sub

[officespacer]

No need to apologize Hans, it was better than i could do myself! it even removes the periods, great!! thanks for the help!