CODEWITHSUNDEEP
excelvba
Sandra
Sandra

Reputation: 1

Copy Transpose Loop over multiple columns

I'm very new at VBA I'm wanting to copy and transpose multiple columns and rows. Bonus if I can get alternating blank columns in between. I can get the first column to move but I'm stuck there. I'm assuming I can make a loop somehow? Here is what I'm trying to do for all data A1 to H12.

enter image description here

Thank you

Upvotes: 0

Views: 73

Answers (1)

FaneDuru
FaneDuru

Reputation: 42236

Please, try the next code:

Sub ProcessRange()
 Dim sh As Worksheet, arr, arrFin, i As Long, j As Long, r As Long, c As Long
 
 Set sh = ActiveSheet
 arr = sh.Range("B3:M10").value 'put the range to be processed in an array
 
 ReDim arrFin(1 To UBound(arr) / 2, 1 To UBound(arr, 2) * 2) 'ReDim the array to keep the processing result
 r = r + 1: c = c + 1           'initialize variables (r = rows, c = columns) for the final array
 For j = 1 To UBound(arr, 2)    'iterate between the processed array columns
    For i = 1 To UBound(arr)    'iterate between the processed array rows
        If i Mod 2 = 1 Then
             arrFin(r, c) = arr(i, j)  'extract the cases of odd rows
        Else
            arrFin(r, c + 1) = arr(i, j): r = r + 1 'extract the case of even rows and increment the row
        End If
        If i = UBound(arr) Then r = 1: c = c + 2    'reinitialize the row variable and increment the column one
    Next i
 Next j
 'drop the processed array content at once:
 sh.Range("B17").Resize(UBound(arrFin), UBound(arrFin, 2)).value = arrFin
End Sub

I make an exception, supposing that you, being new, do not understand the community spirit and rules and answer a question which cannot prove any effort to solve the problem by your own and show us a piece of code, even a not working one.

Please, learn that and ask questions only in the community spirit.

You must learn that we here only help you correct your not working solution.

Upvotes: 1

Related Questions