1

I am working on a Excel VBA Project. I have data in an excel file as such (each alphabet in each cell). I have many lines as such and each line will definitely have data in the first 6 cells, with variable number of 2 cells each thereafter.

a b c d e f g h i j

k l m n o p q r s t

I want to convert it to as such:

a b c d e f 
        g h
        i j 
k l m n o p
        q r
        s t

How do I transpose the values?

Improve this question
2
  • 'Transpose' is such a simple word, but what it looks like you need is a vba program to do this. For Excel this is beyond my abilities, but I use vba in Access to do this sort of thing. You might look for a good book that shows how to use vba with Excell (or Excel with VBA).
    – Elliptical view
    Commented Jan 11, 2014 at 9:51
  • I don't at all understand what you're trying to do. How is the variable determined?
    – Raystafarian
    Commented Jan 11, 2014 at 13:01

1 Answer 1

Reset to default
0

This small macro will take the data from Sheet1 and place it on Sheet2 in a re-organized fashion:

Sub DataReOrganizer()
    Dim s1 As Worksheet, s2 As Worksheet
    Set s1 = Sheets("Sheet1")
    Set s2 = Sheets("Sheet2")
    Dim r1 As Long, r2 As Long, c1 As Long
    Dim N1 As Long
    N1 = Cells(Rows.Count, 1).End(xlUp).Row
    r1 = 1
    r2 = 1
    c1 = 7
    For i = 1 To N1
        s1.Range("A" & r1 & ":F" & r1).Copy s2.Range("A" & r2)
        r2 = r2 + 1
        While s1.Cells(r1, c1) <> ""
            s1.Range(s1.Cells(r1, c1), s1.Cells(r1, c1 + 1)).Copy s2.Range("G" & r2)
            c1 = c1 + 2
            r2 = r2 + 1
        Wend
        r1 = r1 + 1
        c1 = 7
    Next i
End Sub
Improve this answer
1
  • I have an issue though... if the cell with "g" is blank, it doesn't copy "i" and "j"... how do i fix it?
    – user3159346
    Commented Jan 14, 2014 at 3:02

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .