Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Apr 2001
    Location
    Washington, Washington, USA
    Posts
    57
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Auto-Outlining via VBA (VBA/Excel 2000)

    I think I'm just too tired to reason this out. I've been sitting here for half an hour, just looking at my sample sheet, getting fuzzier and fuzzier.

    What I need to do, after the user has filled in the data something like the image I've attached, is have the code go through and automatically group the items in columns 2-4 so that each level can be rolled up. (Apparently, this is what they are used to in MSProject, and they want to do something similar here.) As you'll see, the format doesn't lend itself to Automatic outlining, so I need to group each set based on the offset column, I think. So far, that's as far as my "DEADLINE MONDAY!!!" frozen brain will take me. I've got a dozen other major parts of this project to finish, so I'm going to move on, hoping that someone can give me a push in the right direction in time. As always, many thanks!

    --Karyl

  2. #2
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: Auto-Outlining via VBA (VBA/Excel 2000)

    Karyl,

    My Excel programming is not good enough to do this for you, but here is something to get you started till an expert logs in...

    Use Range.End(xlDown) in the various columns to identify the start and end rows for each group.
    Then use Rows.Group to create each of the level 4 groupings, based on the Work Item column
    Then Rows.Group for level 3 and then for level 2 etc.

    StuartR

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

    Re: Auto-Outlining via VBA (VBA/Excel 2000)

    I don't have the slightest idea, but you would make it easier for others to help you if you posted a spreadsheet with dummy data to work on.

  4. #4
    Star Lounger
    Join Date
    Apr 2001
    Location
    Washington, Washington, USA
    Posts
    57
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto-Outlining via VBA (VBA/Excel 2000)

    I can do that. See attached for sample data and following attached for one that is the way I need it.

    I THINK I've started to figure out the logic to it.

    You start with column four, select the first row (or just the cell--either seems to work) with an entry in it. Select that cell down to the last cell that doesn't have an entry in ANY column to the left (1-3, it doesn't matter). Group those. Move down to the next item in the fourth column following the previous selection. Select from the first item down to the last cell that doesn't have an entry to the left. Group. Then move to column three, doing the same thing. Select the first item in the column down through the last row with nothing to the left. Group. Etc. Then column two. Column one doesn't get grouped.

    I'm sure this isn't exactly clear. It is hard to describe. But in the sample document, I wrote down the steps I follow to do the same thing manually that I need to do with code. Thanks for taking a look.

    --Karyl

  5. #5
    Star Lounger
    Join Date
    Apr 2001
    Location
    Washington, Washington, USA
    Posts
    57
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto-Outlining via VBA (VBA/Excel 2000)

    Second sheet with desired outcome.The two together were to big to send in the same workbook. Sorry I didn't post a sample document in the first place. For some reason, it didn't even occur to me that I could do that! So I sent a picture instead.

    --Karyl

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Auto-Outlining via VBA (VBA/Excel 2000)

    Karyl, for future reference your worksheets are big because they contain a large number of empty formatted cells. If you clear those empty cells using Edit | Clear | All the workbooks save as much smaller. Interesting problem you have posed, no doubt a faster coder than me (and they all are) will beat me to a solution.
    -John ... I float in liquid gardens
    UTC -7ąDS

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

    Re: Auto-Outlining via VBA (VBA/Excel 2000)

    Does the attached version (zipped) do what you want? Make sure to test it in various situations. The code could be made more flexible., but I don't have time for that now.

  8. #8
    Star Lounger
    Join Date
    Apr 2001
    Location
    Washington, Washington, USA
    Posts
    57
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto-Outlining via VBA (VBA/Excel 2000)

    Thank you so much, Hans. It does exactly what I want. At the most, I was hoping for a point in the right direction, so having a working procedure to play with is a great deal more than I expected. I'll work through the code so that I understand what you're doing (I'm definitely WAY too tired to do it now) and then I'll modify it to--hopefully--meet the various needs of the users who will be working with the final product.

    And sorry about the too-large files. I had planned to take out all the conditional formatting before I uploaded, but by the time I got done figuring out exactly how I'd gotten the results I wanted the first time and then writing my explanation, I forgot about it. It also didn't occur to me to just zip the file. I guess I'm out of practice using the lists.

    Again, many thanks. I just love clicking the buttons and watching the outline appear and disappear--like magic! I think that's why I enjoy programming so much. It is just so cool when something works!

    --Karyl

Posting Permissions

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