Display data from rows to column using VBA

Hi i would really need some help here with VBA. I am quite new to programming. I have some data in excel that is display in rows. I want them to display in column instead. Any idea how i can do it? i did some coding but it only display the first line of code.

Initial Result:
John S1 Math 1
John S1 English 2
John S1 Chinese 3
Benny S2 Sci C
Benny S2 Hist D
Benny S2 Geo E
Ken S3 Bio C
Ken S3 Chem C
Ken S3 Phy A
Joyce S4 Lit A
Joyce S4 French E
Joyce S4 German D

Expected result:
John S1 Math A English B Chinese C
Benny S2 Math C English D Geo D
Ken S3 Bio C Chem C Phy A
Joyce S4 Lit A French E German D

How can i go abt doing dis? my coding looks like this:
Private Sub CommandButton1_Click()
x = 1
n = 1
i = 1

For x = 1 To 1
Sheet2.Cells(n, 1).Value = Sheet1.Cells(2, i)
Sheet2.Cells(n, 2).Value = Sheet1.Cells(2, (i + 1))
Sheet2.Cells(n, 3).Value = Sheet1.Cells(2, (i + 2))
Sheet2.Cells(n, 4).Value = Sheet1.Cells(2, (i + 3))
Sheet2.Cells(n, 5).Value = Sheet1.Cells((2 + x), (i + 2))
Sheet2.Cells(n, 6).Value = Sheet1.Cells((2 + x), (i + 3))
Sheet2.Cells(n, 7).Value = Sheet1.Cells((2 + x + 1), (i + 2))
Sheet2.Cells(n, 8).Value = Sheet1.Cells((2 + x + 1), (i + 3))
x = x + 1
n = n + 1
i = i + 1
End Sub


  • see uploaded file, has a VBA macro to switch rows to columns
  • Hi, Thank you for the reply. The sample code is really useful but for my subject, it is a dyanamic data. I cant dim the subject as the sample code stated else i would like to key in a lot of subject.

    Engl As String
    Math As String
    Hist As String
    Geog As String
    Chem As String

    How can i go about doing that?
Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!