Automatic ROW GROUPING (via Marco or other way out) based on certain column (Value)

[Pawiair]

Hello Friends,
I am working on a Project Plan where I have to copy the Tasks (And their Sub Tasks, and their Sub-Sub tasks etc.) from MPP file to xlsx file. Now my requirement is to be able to show the data in the same GROUPABLE format (at different levels) as I see in mpp file.

I am looking out for a AUTOMATIC way of achieving this (VBA etc.) as doing it manually is not a good idea for hundred lines file. The idea (expectation) is that I can decide which level of information (activities), I have to show to different audience.

For e.g. a Management level person would be interested to see only Key activities (Level 1) but a coder (technical team member) would wanna know lowest level information but a project manager would want to have the possibility of seeing all the levels just by clicking LEFT HAND SIDE Levels.

Any help would be useful.

Thanks,

Pawan

 

[PCosta87]

Hi,

Welcome to the forum

Try this:

Sub Group()

    Dim c As Range
    Dim lrow, lvl As Integer
   
    lrow = Cells(Rows.count, "A").End(xlUp).Row
   
    For Each c In Range("A1:A" & lrow)
        lvl = Len(c) - Len(Replace(c, ".", ""))
        For i = 1 To lvl
            c.Rows.Group
        Next i
    Next c
       
End Sub

Hope this helps

 

[Pawiair]

Hello PCosta,

Many thanks friend. It seems, it has worked absolute wonder. I never thought, it would be so easy to do that.

Just two more Qns:
a) Is there any limit of LEVELs (could not find the limit on internet) in MS Excel?
b) Can you send me the code to work with a column where only normal numbers 1,2,3,4,5,6 are given and if I want these numbers to be used for decided the grouping?

Sincere thanks again....

One Large Beer (Or Coffee + Cake) is for sure due on me for your kind help and time (If at all we meet or perhaps I can send you a voucher). I know your time and effort worth alot.


Thanks

 

[PCosta87]

Hi,

First of all, you are welcome... I'm glad I could help

About your questions:

Is there any limit of LEVELs (could not find the limit on internet) in MS Excel?

In my version of excel (2016), from a quick test, it seems I can't have more than 8 "levels". I'm not sure if there is some option hidden away that allows for more.

Can you send me the code to work with a column where only normal numbers 1,2,3,4,5,6 are given and if I want these numbers to be used for decided the grouping?

I don't think I understood the requirement. Do you wish to use those numbers as the level?
If so it should be easy enough... a minor change in the code should do it:

Sub Group()

    Dim c As Range
    Dim lrow, lvl As Integer

    lrow = Cells(Rows.Count, "A").End(xlUp).Row

    For Each c In Range("A1:A" & lrow)
        lvl = c.Value
        For i = 1 To lvl
            c.Rows.Group
        Next i
    Next c
    
End Sub

It will group if value of column A ">0"