How to use code to for stacking repeating grouped columns
Category: [Excel] Demo Available
How to use code to for stacking repeating grouped columns
Requirement:
From the attachment on the link below:
The set out on Sheet1 down to row 9 is the way the user records data. At the end of each day the user needs to copy and paste cols G:M; T:Z;AG:AM.........GQ:GW (haven't shown the full extent on the attachment) one on top of the other using the same cols A:D as an index (at left) on another sheet2. The length of the columns are always the same, but vary in total length each day.
Assistance required with a macro.
Colour formatting isn't a requirement.
Solution:
Code:
Option Explicit
Sub CombineData()
Dim x, y, z(), zz, i As Long, ii As Long, iii As Long, iv As Long, v As Long
With Sheet1
x = .Cells(7, 3).CurrentRegion.Resize(, 4)
For i = 1 To .Rows(6).SpecialCells(2).Areas.Count
y = .Rows(6).SpecialCells(2).Areas(i).Offset(1).Resize(UBound(x, 1), 8)
If ii <> 0 Then v = UBound(z, 2)
ii = ii + UBound(x, 1): ReDim Preserve z(1 To 12, 1 To ii)
For iii = 1 To 4
For iv = 1 To UBound(x, 1)
z(iii, v + iv) = x(iv, iii)
Next
Next
For iii = 5 To 12
For iv = 1 To UBound(y, 1)
z(iii, v + iv) = y(iv, iii - 4)
Next
Next
Next
End With
zz = TransposeArray(z)
With Sheet2
.UsedRange.Offset(1).Clear
.[a2].Resize(UBound(zz, 1), 12) = zz
.Columns(1).Resize(, 5).AutoFit
.Activate
End With
End Sub
Private Function TransposeArray(Arr As Variant) As Variant
Dim temp, a As Long, b As Long, i As Long, ii As Long
i = UBound(Arr, 2): ii = UBound(Arr, 1)
ReDim temp(1 To i, 1 To ii)
For a = 1 To i
For b = 1 To ii
temp(a, b) = Arr(b, a)
Next
Next
TransposeArray = temp
End Function
Obtained from the OzGrid Help Forum.
Solution provided by KjBox.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to group numbers in a pivot table |
| How to match positive and negative values within subgroups |
| How to use a macro or formula to copy data from cell to all cells in that group in adjacent column |
| How to use a macro for grouping rows based on cells with same names |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.