SOLVED

Need a formula or Macro

%3CLINGO-SUB%20id%3D%22lingo-sub-1680397%22%20slang%3D%22en-US%22%3ENeed%20a%20formula%20or%20Macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1680397%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%20I%20need%20a%20way%20to%20shift%20data%20within%20multiple%20columns%20and%20rows.%20To%20go%20from%20looking%20like%20(columns%20A-E)%26nbsp%3B%20to%20looking%20like%20(Columns%20I-M)%20below.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Inkedshift%20data_LI.jpg%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F219270iB9CDFF276BFEE5C5%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22Inkedshift%20data_LI.jpg%22%20alt%3D%22Inkedshift%20data_LI.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1680397%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1681043%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20a%20formula%20or%20Macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1681043%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F797833%22%20target%3D%22_blank%22%3E%40aangus07%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHere%20is%20a%20macro.%20You%20can%20change%20the%20constants%20at%20the%20beginning%20if%20desired.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3ESub%20ShiftData()%0A%20%20%20%20Const%20FirstRow%20%3D%2010%0A%20%20%20%20Const%20StartCol1%20%3D%201%0A%20%20%20%20Const%20StartCol2%20%3D%209%0A%20%20%20%20Const%20NumCols%20%3D%205%0A%20%20%20%20Dim%20SourceCol%20As%20Long%0A%20%20%20%20Dim%20TargetRow%20As%20Long%0A%20%20%20%20Dim%20TargetCol%20As%20Long%0A%20%20%20%20Dim%20LastRow%20As%20Long%0A%20%20%20%20Dim%20NumRows%20As%20Long%0A%20%20%20%20Dim%20i%20As%20Long%0A%20%20%20%20Application.ScreenUpdating%20%3D%20False%0A%20%20%20%20TargetRow%20%3D%20FirstRow%0A%20%20%20%20For%20i%20%3D%201%20To%20NumCols%0A%20%20%20%20%20%20%20%20SourceCol%20%3D%20StartCol1%20%2B%20i%20-%201%0A%20%20%20%20%20%20%20%20TargetCol%20%3D%20StartCol2%20%2B%20i%20-%201%0A%20%20%20%20%20%20%20%20LastRow%20%3D%20Cells(Rows.Count%2C%20SourceCol).End(xlUp).Row%0A%20%20%20%20%20%20%20%20NumRows%20%3D%20LastRow%20-%20FirstRow%20%2B%201%0A%20%20%20%20%20%20%20%20Cells(TargetRow%2C%20TargetCol).Resize(NumRows).Value%20%3D%20_%0A%20%20%20%20%20%20%20%20%20%20%20%20Cells(FirstRow%2C%20SourceCol).Resize(NumRows).Value%0A%20%20%20%20%20%20%20%20TargetRow%20%3D%20TargetRow%20%2B%20NumRows%0A%20%20%20%20Next%20i%0A%20%20%20%20Application.ScreenUpdating%20%3D%20True%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1683836%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20a%20formula%20or%20Macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1683836%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAwesome!%20works%20well%2C%20thank%20you%20very%20much!%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

Hello, I need a way to shift data within multiple columns and rows. To go from looking like (columns A-E)  to looking like (Columns I-M) below.

Inkedshift data_LI.jpg

2 Replies
Highlighted
Best Response confirmed by aangus07 (Occasional Contributor)
Solution

@aangus07 

Here is a macro. You can change the constants at the beginning if desired.

 

Sub ShiftData()
    Const FirstRow = 10
    Const StartCol1 = 1
    Const StartCol2 = 9
    Const NumCols = 5
    Dim SourceCol As Long
    Dim TargetRow As Long
    Dim TargetCol As Long
    Dim LastRow As Long
    Dim NumRows As Long
    Dim i As Long
    Application.ScreenUpdating = False
    TargetRow = FirstRow
    For i = 1 To NumCols
        SourceCol = StartCol1 + i - 1
        TargetCol = StartCol2 + i - 1
        LastRow = Cells(Rows.Count, SourceCol).End(xlUp).Row
        NumRows = LastRow - FirstRow + 1
        Cells(TargetRow, TargetCol).Resize(NumRows).Value = _
            Cells(FirstRow, SourceCol).Resize(NumRows).Value
        TargetRow = TargetRow + NumRows
    Next i
    Application.ScreenUpdating = True
End Sub
Highlighted

@Hans Vogelaar 

Awesome! works well, thank you very much!