MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Shift Cells at a time

Posted by Deepika on April 25, 2000 4:29 AM


I have a address list of 100 people one below the other. For example, it is like this:

# 56, Haines Road,
Suite 67, San Jose
CA - 98123
(989) 234 2463

Now, my problem is that i have to arranage this information row by row. ie. in A1 I must get Deepika, B1 i must get # 56, Haines Road, C1 CA - 98123 and D1 i must get (989) 234 2463.

Kindly help me with this as soon as possible.



Posted by Celia on April 28, 2000 4:17 AM

Is each set of data always exactly 5 rows?
Are there any empty rows, either within the data sets or between the sets?
What about the third row in your example? Do you not want to keep the third row?

Posted by Deepika on April 29, 2000 2:33 AM

Hi, Celia,

Yes, each set of data always is of 5 rows.
There are no empty rows.
I would like to retain the third row also because it is part of the address.


Posted by Celia on April 29, 2000 6:17 PM

Try the following :-
1. Make a back-up copy of your worksheet (in case the macro does not work as intended).
2. Put some headings in row 1 (there must be a heading in cell B1, otherwise the macro will not work properly).
3. Make sure that your data starts at cell A2.
4. Run this macro :-

Sub Transpose()
Dim theCell As Range
Set theCell = Range("B65536").End(xlUp)
Application.ScreenUpdating = False
Do Until theCell.Offset(1, -1) = ""
With theCell
.Offset(2, -1).Resize(4, 1).Copy
.Offset(1, 0).PasteSpecial Paste:=xlAll, Transpose:=True
.Offset(2, 0).Resize(4, 1).EntireRow.Delete
End With
Set theCell = Range("B65536").End(xlUp)
End Sub


Posted by Michelle on May 02, 2000 11:47 AM

There is an easier way if you would like to just change your columns to rows. Highlight the area and hit Copy. Select the first cell in the row you would like to move it to and hit "Paste Special". In this dialog box is a "Transpose" checkbox. Click this and it will change your rows to columns. This will work the same if you want to change from rows to columns or columns to rows. Whatever you highlight and copy the transpose option will do the opposite.

Posted by Celia on May 02, 2000 6:54 PM

This is exactly what the macro does (and does it for all data at once).
Doing it without a macro would mean that you would have to Copy/PasteSpecial/Transpose for each set of data (a total of 100 times based on the original question).