1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: excel macro to move columns to rows

Discussion in 'Business Applications' started by mdsjsb, Mar 8, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. mdsjsb

    mdsjsb Thread Starter

    Joined:
    Mar 8, 2008
    Messages:
    3
    I have a flat file that when I read it into excel it populates column 1 of each row with data.
    This data is actually a series of 5 fields that I need to have in columns and rows, that is,
    column a1 thru a5 I need placed in a1, b1,c1,d1 and e1 followed by
    column a6 thru a10 placed in a2,b2,c2,d2 and e2.
    The data is balanced, that ism there is data in each of the 5 fields for a "record".
    Any help would be appreciated.
    THanks
     
  2. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,545
    This should reconfigure your data to columns 2:6 ; if it's what you need, just add a line at the end to delete the first (original) column.

    Sub test()
    x = WorksheetFunction.CountA(Range("A:A")) / 5
    For i = 1 To x
    Cells(i, 2).Resize(, 5) = Application.Transpose(Cells((i - 1) * 5 + 1, 1).Resize(5))
    Next i
    End Sub
     
  3. mdsjsb

    mdsjsb Thread Starter

    Joined:
    Mar 8, 2008
    Messages:
    3
    I created a new macro called test, copied your code into it, tried to run it and got a Compile Error; syntax error
     
  4. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,545
    Upload a small sample file -- say 15-20 rows of data -- so that we can be sure of your structure.
     
  5. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,545
    Another way, without macros.

    Assuming you have values (say, 1 to 20) in A1:A20, this formula in B1:

    =INDIRECT("A"&COLUMN()-1+((ROW()-1)*5))

    Then copy B1 to B1:F1, and B1:F1 to B1:F4.
     
  6. mdsjsb

    mdsjsb Thread Starter

    Joined:
    Mar 8, 2008
    Messages:
    3
    Just to make sure I didn't copy the macro wrong, I started over and it worked correctly this time. Thank you very much for your help.
    mdsjsb
     
  7. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,545
    Thanks for marking "Solved" & welcome to the board. (y)
     
  8. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/691315

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice