Help with code to re-align data

[richanor]

I have imported 5000+ lines of data which was copied from a .pdf - unfortunately although it was laid out in the .pdf in 4 columns, it has imported into Excel all in one column e.g.

A1=Title 1
A2=Title 2
A3=Title 3
A4=Title 4
A5=Data 1
A6=Data 2
A7=Data 3
A8=Data 4

I need it to be:

A1=Title 1, B1=Title 2, C1=Title 3, D1=Title 4
A2=Data 1, B2=Data 2, C2=Data 3, D2=Data 4

Could anybody help me out with a bit of code that will rearrange this and just loop until it is finished?

Thanks very much in advance,

Rich

 

[Fluff]

This will copy the data into column B onwards in the same sheet.

Sub Richanor()

    Dim UsdRws As Long
    Dim Cnt As Long
    Dim Rw As Long
    
Application.ScreenUpdating = False
    Rw = 1
    UsdRws = Range("A" & Rows.Count).End(xlUp).Row
    For Cnt = 1 To UsdRws Step 4
        Range("A" & Cnt).Resize(4).Copy
        Range("B" & Rw).PasteSpecial Transpose:=True
        Rw = Rw + 1
    Next Cnt
Application.CutCopyMode = False

End Sub

 

[richanor]

Fluff - you absolute beauty! Simple for you, but saved me a lot of time

Thanks

Rich

 

[Fluff]

Glad to help & thanks for the feedback

 

[Rick Rothstein]

Fluff - you absolute beauty! Simple for you, but saved me a lot of time

In case you might be interested, this macro will execute noticeably faster for your 5000+ rows of data...

Sub RealignDataImportedFromPDF()
  Dim R As Long, C As Long, Data As Variant, Result As Variant
  Data = Range("A1", Cells(Rows.Count, "A").End(xlUp).Offset(3))
  ReDim Result(1 To UBound(Data), 1 To 4)
  For R = 1 To UBound(Data) - 3 Step 4
    For C = 1 To 4
      Result((R + 3) / 4, C) = Data(R + C - 1, 1)
    Next
  Next
  Range("B1").Resize(UBound(Result), 4) = Result
End Sub