Transpose Data automatically

My data looks like this: (each cell contains 1 item of data)

Apple
15
1
2
3
<blank cell>
Pear
1
3
4
7
<blank cell>

Etc... for 100's of line. So name of fruit then calorific values underneath

I need the data to look like this:

Apple 15 1 2 3
Pear 1 3 4 7

Etc..

Then I can apply Vlookup functions, so if your enter a food name you get all the relevant data.

Dave32

Your not on the same website. We just police cross posters. Read forum rules and message to cross posters below to understand why

VBA Noob

Hi,

Not particularly neat and tidy, but this will do the job. Alter the 1000 below according to how many rows you have

Sub Macro1()

Dim source_row, destination_row As Integer
For source_row = 0 To 1000 Step 7
Range(Cells(1, 1).Offset(source_row, 0), Cells(1, 1).Offset(6 + source_row, 0)).Select
    Selection.Copy
    Cells(1, 3).Offset(destination_row, 0).Select
    destination_row = destination_row + 1
        Selection.PasteSpecial Transpose:=True
Next source_row

End Sub