Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2011
    Posts
    8

    Unanswered: VB Macro to move columns to rows

    Hi,

    I have a file from a report that looks like this:

    Column1 Column2
    3172536 10
    3172839 10
    --------- 20
    1111100 10
    -------- 20
    -------- 30
    -------- 40
    -------- 50
    --------- 60
    2918288 10
    -------- 20


    The dashes are supposed to be spaces (blank cells).

    I want the output to look like this:
    Column1 Col2 Col3 Col4 Col5 Col6 Col7
    3172536 10
    3172839 10 20
    1111100 10 20 30 40 50 60
    2918288 10 20

    Can someone please help me?

    Thank you,
    Jeepman

  2. #2
    Join Date
    Oct 2011
    Posts
    8
    Also, if someone can help me. I would also like to know how to do the first macro but also move columns B, C, D, E, and F to the corresponding row. I can copy the value in column a down to the blank cells so there is a match if that is easier.

  3. #3
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    I think you nee something like this pehaps?
    Code:
    Sub CollateData()
        Dim iRow As Long
        Dim j As Integer
        Dim iColumnOne As Integer
        
        iColumnOne = 1
        iRow = 2
        Do Until Cells(iRow, iColumnOne) = "" And Cells(iRow, iColumnOne + 1) = ""
            j = 2
            With Cells(iRow + 1, iColumnOne)
                Do Until .Offset(1, 0) <> "" Or .Offset(1, 1) = ""
                    .Offset(0, j) = .Offset(1, 1)
                    Rows(.Offset(1, 1).Row).EntireRow.Delete
                    j = j + 1
                Loop
            End With
            iRow = iRow + 1
        Loop
    End Sub
    This assumes the data is in columns A and B, starts on line 2 and column B has continuous data (no blank lines).

    HTH


    MTB

  4. #4
    Join Date
    Oct 2011
    Posts
    8
    MTB,

    Thank you so much. It works great. You are saving me so much time.

    Now how would the code look if I wanted to use the same logic but move columns C-O and move to P-AB.

    So
    Column1 Column2 Col3(C) Col4(D) etc.
    3172536 abcdef 10
    3172839 abcccc 10
    --------- ------ 20
    1111100 abcdjjjj 10
    -------- ------- 20
    -------- ------- 30
    -------- ------- 40
    -------- ------- 50
    --------- ------- 60
    2918288 abdfggg 10
    -------- ------- 20



    Thanks,
    JeepMan

  5. #5
    Join Date
    Oct 2011
    Posts
    8
    MTB,

    To make it simpler - we can just move columns C-I with up to 8 iterations. Is this difficult?

    Thanks again,
    Dan

Posting Permissions

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