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

S

ScotSquad

New Member
Joined
Apr 21, 2017
Messages
6
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!
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Sort by date Sort by votes
Re: Moving Manager Hierarchy from "LEFT TO RIGHT" to "RIGHT TO LEFT" using vba code - HELP!

ScotSquad said:
Hi all,

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.

Thanks in advance!
Click to expand...

Looking at your before and after I don't see how inserting cells (you can only insert rows or columns) is needed. You just need to move the text to the cell on the right if it is blank. Is this what you need?

Edit: is your table always 6 columns across or does the code need to be dynamic?
 
Last edited:
Upvote 0
Re: Moving Manager Hierarchy from "LEFT TO RIGHT" to "RIGHT TO LEFT" using vba code - HELP!

Try...

Code: 
Sub InsCells()
    Dim i As Long, lr As Long
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    For i = 2 To lr
        Cells(i, 3).Resize(, Cells(i, 2).Value).Insert Shift:=xlToRight
    Next
End Sub
 
Upvote 0
Re: Moving Manager Hierarchy from "LEFT TO RIGHT" to "RIGHT TO LEFT" using vba code - HELP!

This works but I know it can be much 'neater' I tried yours mark but it errored out.

A neater way would be just to select all managers and 'move them' number of column shown in col B but this does work :)

Code: 
Sub RightAlign()
    Dim lr As Long, i As Long, x As Long, y As Long
    Dim iBlanks As Integer
    Dim rRightManager As Range 'rightmost manager
    
    'Get last row
    lr = Range("A" & Rows.Count).End(xlUp).Row
    
    'loop through all rows
    For i = 2 To lr
        'Get number of blanks
        iBlanks = Range("B" & i).Value
        
        'Get The rightmost manager
        Do Until Range("C" & i).Offset(0, x) = ""
            x = x + 1
        Loop
        Set rRightManager = Range("C" & i).Offset(0, x - 1) 'Rightmost manager will be x-1
        
        'Starting with right most manager move managers across
        For y = rRightManager.Column To 3 Step -1
            Cells(i, y).Offset(0, iBlanks) = Cells(i, y)
            Cells(i, y) = ""
        Next y
        
    Next i
    
End Sub
 
Upvote 0
Re: Moving Manager Hierarchy from "LEFT TO RIGHT" to "RIGHT TO LEFT" using vba code - HELP!

gallen said:
I tried yours mark but it errored out
Click to expand...

The only time the code errors out on me is if there is a blank or no value in column B as you can't resize by zero and the OP's post shows that they already have the number of blanks in column B.

ScotSquad said:
depending on the amount of blank values (which I've got a count of for each row in column B).
Click to expand...

Another way...

Code: 
Sub InsCells2()
    Dim i As Long, lr As Long, lc As Long, x As Long
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    lc = Cells(1, Columns.Count).End(xlToLeft).Column
    x = Range(Cells(1, 3), Cells(1, lc)).Count
    For i = 2 To lr
        Cells(i, 3).Resize(1, x - Cells(i, 2).Value).Copy
        Cells(i, 3 + Cells(i, 2).Value).PasteSpecial xlPasteValues
        Cells(i, 3).Resize(1, Cells(i, 2).Value).ClearContents
    Next
End Sub

Based on the ranges and info below (as was the first code I posted).


Excel 2010
ABCDEF
1EmployeeBlank Values in HierarchyLine 1 ManagerLine 2 ManagerLine 3 ManagerLine 4 Manager
2Employee 12Manager BManager A
3Employee 23Manager A
4Employee 31Manager CManager BManager A
5Employee 42Manager DManager A
6Employee 53Manager A
Sheet1
 
Last edited:
Upvote 0
Re: Moving Manager Hierarchy from "LEFT TO RIGHT" to "RIGHT TO LEFT" using vba code - HELP!

Just for completeness, a cut method that doesn't error out if a zero or blank in column B...

Code: 
Sub InsCells()
    Dim i As Long, lr As Long, lc As Long, x As Long

    lr = Cells(Rows.Count, "A").End(xlUp).Row
    lc = Cells(1, Columns.Count).End(xlToLeft).Column

    x = Range(Cells(1, 3), Cells(1, lc)).Count
    For i = 2 To lr
        If Cells(i, 2) <> "" And Cells(i, 2) <> 0 Then
            Cells(i, 3).Resize(1, x - Cells(i, 2).Value).Cut Cells(i, 3 + Cells(i, 2).Value)
        End If
    Next
End Sub

and the same for the paste version...

Code: 
Sub InsCells2()
    Dim i As Long, lr As Long, lc As Long, x As Long
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    lc = Cells(1, Columns.Count).End(xlToLeft).Column
    x = Range(Cells(1, 3), Cells(1, lc)).Count
    For i = 2 To lr
        If Cells(i, 2) <> "" And Cells(i, 2) <> 0 Then
            Cells(i, 3).Resize(1, x - Cells(i, 2).Value).Copy
            Cells(i, 3 + Cells(i, 2).Value).PasteSpecial xlPasteValues
            Cells(i, 3).Resize(1, Cells(i, 2).Value).ClearContents
        End If
    Next
End Sub
 
Last edited:
Upvote 0
Re: Moving Manager Hierarchy from "LEFT TO RIGHT" to "RIGHT TO LEFT" using vba code - HELP!

I tried the cut, but couldn't get it to paste values only so it removed all formatting too.

This is a condensed version of my first post too (yes it's quiet at work)

Code: 
Sub RightAlign()
Dim i As Long, x As Long, y As Long
    For i = 2 To Range("A" & Rows.Count).End(xlUp).Row
        Do Until Range("C" & i).Offset(0, x) = ""
            x = x + 1
        Loop
        For y = Range("C" & i).Offset(0, x - 1).Column To 3 Step -1
            Cells(i, y).Offset(0, Cells(i, 2)) = Cells(i, y)
            Cells(i, y) = ""
        Next y
    Next i
End Sub
 
Upvote 0
Re: Moving Manager Hierarchy from "LEFT TO RIGHT" to "RIGHT TO LEFT" using vba code - HELP!

gallen said:
I tried the cut, but couldn't get it to paste values only
Click to expand...

You can't cut and paste values, it's not how cut works.
 
Upvote 0
Re: Moving Manager Hierarchy from "LEFT TO RIGHT" to "RIGHT TO LEFT" using vba code - HELP!

Hi guys,

Thanks for the help so far.

Rather than copying and pasting the values (I've tried copying and pasting values and had various issues with this as the number of "manager" columns varies!).

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
 
Upvote 0
Re: Moving Manager Hierarchy from "LEFT TO RIGHT" to "RIGHT TO LEFT" using vba code - HELP!

I don't think the number of columns matters in either code.

Mine should work no matter how many columns there are though would error if there is a blank cell between 1 manager and the next
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Can a macro help create a user hierarchy?
Replies
7
Views
687
Akuini
Akuini
R
  • Question Question
Smarter alternative to Name Manager & Indirect Formula?
Replies
1
Views
380
RoryA
RoryA
W
  • Question Question
Move Row to Another Sheet Based on Multiple Cell Values
Replies
9
Views
484
jolivanes
jolivanes
Z
  • Solved
Formula to validate the right manager within manager levels in multiple rows
Replies
5
Views
277
Fluff
Fluff
Z
  • Question Question
VBA Process Flow Chart using Shapes & Connectors
Replies
2
Views
180
zlotowlosapl
Z

Forum statistics

Threads
1,223,886
Messages
6,175,198
Members
452,617
Latest member
Narendra Babu D

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top