Moving Manager Hierarchy from "LEFT TO RIGHT" to "RIGHT TO LEFT" using vba code

[ScotSquad]

Hi all,

I have a simplified version of a sheet I'm working with below showing the data as it is, and how I'd like it to be....
CURRENTLY...
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Employee
[/TD]
[TD]Blank Values in Hierarchy
[/TD]
[TD]Line 1 Manager
[/TD]
[TD]Line 2 Manager
[/TD]
[TD]Line 3 Manager
[/TD]
[TD]Line 4 Manager
[/TD]
[/TR]
[TR]
[TD]Employee 1
[/TD]
[TD]2
[/TD]
[TD]Manager B
[/TD]
[TD]Manager A
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Employee 2
[/TD]
[TD]3
[/TD]
[TD]Manager A
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Employee 3
[/TD]
[TD]1
[/TD]
[TD]Manager C
[/TD]
[TD]Manager B
[/TD]
[TD]Manager A
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Employee 4
[/TD]
[TD]2
[/TD]
[TD]Manager D
[/TD]
[TD]Manager A
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Employee 5
[/TD]
[TD]3
[/TD]
[TD]Manager A
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

HOW I'D LIKE IT TO BE...
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Employee
[/TD]
[TD]Blank Values in Hierarchy
[/TD]
[TD]Line 1 Manager
[/TD]
[TD]Line 2 Manager
[/TD]
[TD]Line 3 Manager
[/TD]
[TD]Line 4 Manager
[/TD]
[/TR]
[TR]
[TD]Employee 1
[/TD]
[TD]2
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Manager B
[/TD]
[TD]Manager A
[/TD]
[/TR]
[TR]
[TD]Employee 2
[/TD]
[TD]3
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Manager A
[/TD]
[/TR]
[TR]
[TD]Employee 3
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD]Manager C[/TD]
[TD]Manager B
[/TD]
[TD]Manager A
[/TD]
[/TR]
[TR]
[TD]Employee 4
[/TD]
[TD]2
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Manager D
[/TD]
[TD]Manager A
[/TD]
[/TR]
[TR]
[TD]Employee 5
[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Manager A[/TD]
[/TR]
</tbody>[/TABLE]

Essentially what I'm trying to do is "right-align" the manager hierarchy by moving the hierarchy values to the right depending on the amount of blank values (which I've got a count of for each row in column B). For example, in row 2 (Employee 1) I want to insert 2 cells at C2 to move the hierarchy along by 2. However in row 4 (Employee 3), I'd only want to insert 1 cell at C4 as the data only needs to move along by 1 cell as there is only 1 blank value etc. etc.

Sadly it's not possible to do this manually as I have a sheet with over 1,000 rows so I'm hoping that there is a quick VBA solution that could go through each row by row, checking the value in column B, and inserting the appropriate number of cells at C2, C3, C4 etc. in order to move the data along to "right align" it if that makes sense.

I'd appreciate any help or solution that could be provided, or if anyone can think of a more elegant solution I'd love to hear it!

Thanks in advance!

 

[MARK858]

Re: Moving Manager Hierarchy from "LEFT TO RIGHT" to "RIGHT TO LEFT" using vba code - HELP!

I was hoping that it would be possible to insert/shift right cells row-by-row (with the number of cells inserted/shifted based on the value in the "Blank Values in Hierarchy" cell, I can do this manually so I assumed there could be a quick macro/vba script that could do this for the other 999 records!!

Thanks

That is what the first code in post #6 does (you obviously haven't tested it) but as it is cutting it also moves any formatting (It could seriously do with having screenupdating turned off though )

Copying and pasting values is the better solution and as gallen has stated the number of columns doesn't affect any of the later codes posted if you have issues with the values then post the actual issues encountered.

 

[ScotSquad]

Re: Moving Manager Hierarchy from "LEFT TO RIGHT" to "RIGHT TO LEFT" using vba code - HELP!

That is what the first code in post #6 does (you obviously haven't tested it) but as it is cutting it also moves any formatting (It could seriously do with having screenupdating turned off though )

Copying and pasting values is the better solution and as gallen has stated the number of columns doesn't affect any of the later codes posted if you have issues with the values then post the actual issues encountered.

Hi Mark,

Yes, you're right, I was out of the office this morning and so wasn't able to test your solution. I've tried it there and it works a treat, even with the 8 layers of management I have here!

Thanks again for your help with this! Much appreciated! :D