'Loop through each column in the range
For Each SourceColumn In Sourcerange.Columns
'Loop through each cell in the column
For Each SourceCell In SourceColumn.Cells
'If source cell is empty, then move destination cell to
'the beginning of the next row, unless destination cell
'is in the first column
If IsEmpty(SourceCell) Then
If DestCell.Column > 1 Then
Set DestCell = DestWs.Cells(DestCell.Row + 1, 1)
End If
'If source cell has a value, write it to destination cell
'and move destination cell one step to the right
Else
DestCell.Value = SourceCell.Value
Set DestCell = DestCell.Offset(0, 1)
End If
Next SourceCell

'Move destination cell to the beginning of the next row
'unless destination cell is in the first column
If DestCell.Column > 1 Then
Set DestCell = DestWs.Cells(DestCell.Row + 1, 1)
End If
Next SourceColumn
End Sub

Antrats macro will only work if you have litterally named your records 'Record1', 'Record2',...

If that is not the case, is there any other way to find out a pattern, are they spread in a logical way over the sheet, or just at random ? The difficulty here will be to find the records on your original sheet, not the paste special.

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

If your data is on Sheet1 and arranged as I wrote in my previous post, this formula will help you. Enter the formula in cell A1 on Sheet2:

=INDEX(Sheet1!$A:$A,ROW()*5-(5-COLUMN()))&""

Copy the formula from A1 into A1:E1 and then copy the range A1:E1 as far as needed. This will give you all records from Column A on Sheet1.

Use Sheet3 to exctract all records from column B. Enter this formula in cell A1 and copy in the same way as the previous formula.

=INDEX(Sheet1!$B:$B,ROW()*5-(5-COLUMN()))&""

When you have used these formulas to transpose all records, copy the lists from Sheet2 and Sheet3 and Paste Special/Values into a final destination sheet.

Ture Magnusson
Karlstad, Sweden

0

SilverprexAuthor Commented: 2000-05-15

Antrat I did not litereally mean record1 as record1 .
Ture

My records are in column A and B . No big deal if the 1st record located in Cell a1 or A2 or A3 right..? Last NO , not exactly 5 cells high ..There might be 6 and there r 4 cells high. Wht separates each record is a single or double row field..U think anyone of you knows how to code a Macro on that ..? (That if the records reaches a blank field it then go to the next field for transpose')

'Fill first row of new sheet with Field1 to Field6
For i = 1 To 6
ns.Cells(1, i) = "Field" & i
Next i

'Initial value for variable r, counting the actual row number on new sheet
r = 2
'Reset i to 1 (just a row counter)
i = 1
'Look for first row in column A with data
If os.Cells(i, 1) = "" Then i = os.Cells(i, 1).End(xlDown).Row

'The loop for column A
While i < os.Cells(1, 1).SpecialCells(xlCellTypeLastCell).Row
'ni is just an intermediate variable
ni = os.Cells(i, 1).End(xlDown).Row
'Copy the record just found
os.Range(os.Cells(i, 1), os.Cells(i, 1).End(xlDown)).Copy
'Paste it into new sheet, transposed, on new row
ns.Cells(r, 1).PasteSpecial Paste:=xlPasteAll, Transpose:=True
'Add one to the paste row number
r = r + 1
'Find next line in column A with data
i = os.Cells(ni, 1).End(xlDown).Row
Wend

'Reset counter to 1 for loop in B column
i = 1
'Once again, look for first non-empty cell in column B
If os.Cells(i, 2) = "" Then i = os.Cells(i, 2).End(xlDown).Row

Calacc'
I select all the records from column a then Run the Macro but then I got this error' :
"The information cannot be pasted because the copy are and the paste are are not the same size and shape. Try one of the following
* Click a single cell and then paste
* select a rectangle that's the same size and shape, and then paste "

Hows this one? It adds a sheet called "transposed" to transpose your data onto. It also inserts a column so that column B becomes Column C and then puts it back, this is so the CurrentRegion method will work properly.

'put a blank column between.
FromSheet.Columns("B:B").Insert
'Set ii to become the first row # with data
ii = FromSheet.Columns("A:A").End(xlDown).Row

'Do as many times as there are areas _
in column A
For i = 1 To FromSheet.Columns _
("A:A").SpecialCells(xlCellTypeConstants, 23).Areas.Count
FromSheet.Cells(ii, 1).CurrentRegion.Copy
ToSheet.Cells(i, 1).PasteSpecial Transpose:=True
ii = FromSheet.Cells(ii, 1).CurrentRegion.End _
(xlDown).End(xlDown).Row
Next

'ReSet ii to become the first row # with data _
in what was column B
ii = FromSheet.Columns("C:C").End(xlDown).Row
'Do as many times as there are areas _
in column C (which was B)
For i = 1 To FromSheet.Columns _
("C:C").SpecialCells(xlCellTypeConstants, 23).Areas.Count
FromSheet.Cells(ii, 3).CurrentRegion.Copy
ToSheet.Columns("A:A").End(xlDown).Offset _
(1, 0).PasteSpecial Transpose:=True
ii = FromSheet.Cells(ii, 3).CurrentRegion.End _
(xlDown).End(xlDown).Row
Next

'Set ii to become the first row # with data
ii = FromSheet.Columns("A:A").End(xlDown).Row

'Do as many times as there are areas _
in column A
For i = 1 To FromSheet.Columns _
("A:A").SpecialCells(xlCellTypeConstants, 23).Areas.Count
FromSheet.Cells(ii, 1).CurrentRegion.Copy
ToSheet.Cells(i, 1).PasteSpecial Transpose:=True
ii = FromSheet.Cells(ii, 1).CurrentRegion.End _
(xlDown).End(xlDown).Row
Next

'ReSet ii to become the first row # with data _
in what was column B
ii = FromSheet.Columns("C:C").End(xlDown).Row

'Do as many times as there are areas _
in column C (which was B)
For i = 1 To FromSheet.Columns _
("C:C").SpecialCells(xlCellTypeConstants, 23).Areas.Count
FromSheet.Cells(ii, 3).CurrentRegion.Copy
ToSheet.Columns("A:A").End(xlDown).Offset _
(1, 0).PasteSpecial Transpose:=True
ii = FromSheet.Cells(ii, 3).CurrentRegion.End _
(xlDown).End(xlDown).Row
Next