Aug 05 2021 07:27 PM
I have a set of large data in following manner:
A
A.1
A.2
A.3
A.4
A.5
(one/couple of blank rows as separator)
B
B.1
B.2
B.3
B.4
B.5
I want to convert it to:
A A.1 A.2 A.3 A.4 A.5
B B.1 B.2 B.3 B.4 B.5
Aug 05 2021 10:02 PM
If you are open to a VBA solution, please place the following code on a Standard Module like Module1.
In the attached, click on the Transpose Data button to get the desired output.
Sub TransposeData()
Dim lr As Long
Dim rngArea As Range
Dim Rng As Range
Dim destCol As Long
Dim r As Long
Application.ScreenUpdating = False
lr = Cells(Rows.Count, "A").End(xlUp).Row
destCol = 5
On Error Resume Next
Set rngArea = Range("A2:A" & lr).SpecialCells(xlCellTypeConstants, 3)
On Error GoTo 0
Cells(1, destCol).CurrentRegion.Offset(1).Clear
r = 2
If Not rngArea Is Nothing Then
For Each Rng In rngArea.Areas
Rng.Copy
Cells(r, destCol).PasteSpecial xlPasteAll, Transpose:=True
r = r + 1
Application.CutCopyMode = 0
Next Rng
End If
Cells(2, destCol).Select
Application.ScreenUpdating = True
End Sub
Aug 06 2021 07:39 PM
@Subodh_Tiwari_sktneer Thank you for your response, I really appreciate. But unfortunately I don't know how to use VBA, hence looking for some alternatives for my use case.