Thursday, 1 February 2018

excel - Copy every two cells and transpose



I am trying to transpose every next two cells and paste them in next right cells.



I have a table as shown in the screenshot:



screenshot




I want to copy range "B2:B3" and transpose this to "C2" and then loop until there is some data in column B. (so select and copy next "B4:B5" and transpose this to "B4").



I cannot get this to transpose in the right place and then loop.



I have something like this (I did not add loop yet to this macro):



Sub Macro1()
Dim a As Long, b As Long
a = ActiveCell.Column
b = ActiveCell.Row


Range("B2").Select
Range(ActiveCell, Cells(b + 1, a)).Select
Selection.Copy
End Sub

Answer



a VBA solution



Option Explicit


Sub main()
Dim pasteRng As Range
Dim i As Long

With ActiveSheet
Set pasteRng = .Range("C1:D2")
With .Range("B2:B" & .Cells(.Rows.count, "B").End(xlUp).Row)
For i = 1 To .Rows.count Step 2
pasteRng.Offset(i).Value = Application.Transpose(.Cells(i, 1).Resize(2))

Next i
End With
End With
End Sub

No comments:

Post a Comment

casting - Why wasn't Tobey Maguire in The Amazing Spider-Man? - Movies & TV

In the Spider-Man franchise, Tobey Maguire is an outstanding performer as a Spider-Man and also reprised his role in the sequels Spider-Man...