Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Treeview macro (2k)

    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.
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Treeview macro (2k)

    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

  3. #3
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Treeview macro (2k)

    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>
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Treeview macro (2k)

    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

  5. #5
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Treeview macro (2k)

    No need to apologize Hans, it was better than i could do myself! it even removes the periods, great!! thanks for the help!
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •