Sep 17 2020 11:42 AM - edited Sep 17 2020 11:46 AM
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.
Sep 17 2020 01:55 PM
SolutionHere 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
Sep 18 2020 07:04 AM
Awesome! works well, thank you very much!
Sep 17 2020 01:55 PM
SolutionHere 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