I have an existing Excel macro used to shift certain column data into rows. Here it is:
Dim arr As Variant
Dim r As Long
Dim c As Long
Dim Counter As Long
arr = ActiveSheet.UsedRange.Valu
[a1:c1].Value = Array("Date", "Store Number", "Employee Name")
Counter = 1
For r = 2 To UBound(arr, 1)
For c = 3 To UBound(arr, 2) Step 2
If arr(r, c) <> "" Then
Counter = Counter + 1
Cells(Counter, 1) = arr(r, 1)
Cells(Counter, 2) = arr(r, 2)
Cells(Counter, 3) = arr(r, c)
Cells(Counter, 4) = arr(r, c + 1)
How would I modify it to achieve the expected results in the Expected Results worksheet of the attached Excel file? The "Starting File" worksheet is what I'm starting out with. I'm trying to shift each of the "From" and "To" columns ( whether there is data in them or not ) into new rows.
Columns 1 through 3 need to be copied into each of these new rows to maintain the correct relationship. I want to wind up with 5 columns. My worksheet starts out with 19 columns.
I have included only two rows of data in my example but in practice, there are usually many hundreds.