# Thread: Arrange Web Imported Data Horizontally Excel 2007

1. ## Arrange Web Imported Data Horizontally Excel 2007

Hi All,

I like to have some code or formula that arranges my imported data into horizontal order as shown in my attached workbook, please.

It starts at A23 to A25 on sheet WEB and should be moved to sheet DATA from A3 to C3 - same procedure for B23 to B25 on WEB and E3 to G5 on DATA.

All the way down to A82 and B82 on WEB.

The imported data change their values but the format stays.

Thank you very much for your time...

Best regards,
Wolfgang

2. Wolfgang,

The following code will transpose your data from the Web sheet to the data sheet. Place in a standard module and run by clicking on the button on the data sheet.

HTH,
Maud

Wolfgang1.png

Code:
```Public Sub TransposeData()
Application.ScreenUpdating = False
Row = 3
For I = 23 To 80 Step 3
Range("A" & Row & ":C" & Row).FormulaArray = "=TRANSPOSE(WEB!\$A\$" & I & ":\$A\$" & I + 2 & ")"
Range("E" & Row & ":G" & Row).FormulaArray = "=TRANSPOSE(WEB!\$B\$" & I & ":\$B\$" & I + 2 & ")"
Range("I" & Row & ":K" & Row).FormulaArray = "=TRANSPOSE(WEB!\$C\$" & I & ":\$C\$" & I + 2 & ")"
Row = Row + 1
Next I
Application.ScreenUpdating = True
End Sub```

3. ## The Following User Says Thank You to Maudibe For This Useful Post:

wolfgang (2014-11-01)

4. Hi Maude,

read it, applied it, thanked it...

Have a great weekend...

Best
Wolfgang

5. To avoid leaving a formula or formula=value just use
Range("A" & Row & ":C" & Row).value=application.transpose(etc).value

6. dg,

Thanks,
Maud

7. Option Explicit
Sub TransposeSAS()
Dim c As Integer
Dim lc As Integer
Dim i As Long

With ActiveSheet
.Columns.ClearContents
For c = 1 To 3
lc = Cells(1, Columns.Count).End(xlToLeft).Column + 2 '(2)
For i = 23 To 80 Step 3
Cells(i - 22, lc).Resize(, 3).Value = _
Application.Transpose(Sheets("web").Cells(i, c).Resize(3))
Next i
Next c
.Columns.AutoFit
.Columns(3).SpecialCells(xlCellTypeBlanks).EntireR ow.Delete
End With
End Sub

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•