Results 1 to 8 of 8
  1. #1
    Lounger
    Join Date
    Mar 2005
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Moving multimple colums into one row (Office XP SP3)

    I probably won't explain this very well but here goes. Lets say I've got four columns of numbers, say A1-A5, B1-B5, C1-C5, D1-D5. Want I want is for all these cells to be in row one. So, I select cells A2-A5, B2-B5, C2-C5, D2-D5 and drag to cell E1, hoping that the copied cells will fill in row one, but they don't. Is there a way to do that? Otherwise I have to keep copying and pasting until all the columns/cells are in the one row. I was thinking maybe a macro, but the ranges will be different each time so I don't think that will work.

    Thanks for your help.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Moving multimple colums into one row (Office XP SP3)

    You'll have to provide more precise information. How should cells be moved? For example

    1) A2 to B1, A3 to C1, A4 to D1, A5 to E1, B1 to F1, B2 to G1, etc.

    or

    2) A2 to E1, A3 to F1, A4 to G1, A5 to H1, B2 to I1, B3 to J1, etc.

    or something else?

    Moreover, you say the ranges will be different each time. How can we know which ranges are to be moved?

  3. #3
    Lounger
    Join Date
    Mar 2005
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Moving multimple colums into one row (Office XP SP3)

    I would want A2 to go to E1, A3 to F1, A4 to G1, A5 to H1...B2 to I1, B3 to J1, B4 to K1, B5 to L1...C2 to M1...etc. These are report data copied from various Internet sites, so the amount of data will vary, which is what I meant by the range will be different each time. I just want to take a block of cells, highlight them starting with the second row, drag to the first empty cell in the first row, and have the data fill in the cells in that first row. Hope that made more sense, and thanks for your help.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Moving multimple colums into one row (Office XP SP3)

    The following macro will work if you have no more than 256 values (the number of available columns).

    Sub MoveCells()
    Dim lngRow As Long
    Dim lngCol As Long
    Dim lngMaxRow As Long
    Dim lngMaxCol As Long
    Dim lngTarget As Long
    lngTarget = Range("IV1").End(xlToLeft).Column
    lngMaxRow = Cells.Find(What:="*", SearchOrder:=xlByRows, _
    SearchDirection:=xlPrevious).Row
    For lngRow = 2 To lngMaxRow
    lngMaxCol = Range("IV" & lngRow).End(xlToLeft).Column
    For lngCol = 1 To lngMaxCol
    If Not Cells(lngRow, lngCol) = "" Then
    lngTarget = lngTarget + 1
    If lngTarget = 257 Then
    MsgBox "Sorry, no more columns.", vbExclamation
    Exit Sub
    End If
    Cells(lngRow, lngCol).Cut Destination:=Cells(1, lngTarget)
    End If
    Next lngCol
    Next lngRow
    End Sub

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Moving multimple colums into one row (Office XP SP3)

    A simple way without a macro is to put this formula in E1:
    =INDEX($A$2:$D$5,MOD(COLUMN()-COLUMN($E$1),ROWS($A$2:$D$5))+1,INT((COLUMN()-COLUMN($E$1))/ROWS($A$2:$D$5))+1)

    You can then copy it to F1:T1. Then paste- special values (chage the range desired and the starting location as appropriate)

    Steve

  6. #6
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Moving multimple colums into one row (Office XP SP3)

    Steve.....A SIMPLE WAY????....I think Hans's macro is easier than that formula.... <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>
    Regards,
    Rudi

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Moving multimple colums into one row (Office XP SP3)

    Some people do not like macros (they give that "nasty" macro warning) and, unlike most macros, formulas can be undone <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    Steve

  8. #8
    Lounger
    Join Date
    Mar 2005
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Moving multimple colums into one row (Office XP SP3)

    Thanks, I appreciate the responses and expertise. Hopefully this will help.

Posting Permissions

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