Forum Discussion

arifs1870's avatar
arifs1870
Copper Contributor
Jul 08, 2022

Seperating Values

Good Morning,

 

I have a list of parts and warehouses in the same column. the warehouses are right-aligned and the parts are left-aligned. How can I get the warehouses to the right and then expand each part down for the warehouses? Attached is an image of the issue, a part can have multiple warehouses it goes to. 

excel

1 Reply

Sort By
  • GeorgieAnne's avatar
    GeorgieAnne
    Iron Contributor
    Jul 08, 2022
    Hello Shaun,
    I came up with this VBA code solution that is UNTESTED fully and you should try it on your own risk. Make a backup copy and then another copy and test this code on a copy of your workbook.

    Sub MoveValuesBasedOnAlignment()
    Dim objCel As Range

    For Each objCel In Selection.Cells
    If objCel.HorizontalAlignment = xlRight Then
    objCel.Cut Destination:=Cells(objCel.Row, objCel.Column + 2)
    ElseIf objCel.HorizontalAlignment = xlLeft Then
    objCel.Cut Destination:=Cells(objCel.Row, objCel.Column + 1)
    End If
    Next objCel
    End Sub

    Make sure you select the range you want to process FIRST, then run the code. If you are new to VBA then search for how to make a Module and copy and paste the code in it.
    If you have any questions let me know.
    Georgie Anne

Resources