Shift Cells at a time

Deepika
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?
Celia

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.

Deepika

Deepika
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)
Loop
End Sub

Celia

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.

Michelle
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).
Celia