Results 1 to 8 of 8

20060531, 16:48 #1
 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 A1A5, B1B5, C1C5, D1D5. Want I want is for all these cells to be in row one. So, I select cells A2A5, B2B5, C2C5, D2D5 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.

20060531, 16:56 #2
 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?

20060615, 14:27 #3
 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.

20060615, 14:49 #4
 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

20060615, 15:07 #5
 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

20060615, 15:23 #6
 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

20060615, 16:01 #7
 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

20060616, 17:51 #8
 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.