Hi Experts, I have an Excel sheet with the following data:

A1: Name1

A2: Company1

A3: Address1

A4: Telephone1

A5:

A6: Name2

A7: Company2

A8: Address2

A9: Telephone2

A10: Fax2

A11: Email2

A12:

A13: Name3

A14: Company3

A15: Address3

A16: Emailand so on..

There's always a blank row separating each block of data. I want to arrange these data into rows. Any idea on how to do it faster? Doing the copy+paste special+transpose seems to be very tedious. Looking forward for your suggestions.

Thanks in advance!

Run this code against the data:

Sub ColumnsToRows()

'Determine last row with data

lastRw = Range("A" & Rows.Count).End(xlUp).Row

'Initialize Column and Row variables

colNum = 1

rowNum = 1

'Loop through data

For nxtItem = 1 To lastRw

'If there's data in Column A, copy it to B, C, D, etc.

If Cells(nxtItem, 1) <> "" Then

colNum = colNum + 1

Cells(rowNum, colNum) = Cells(nxtItem, 1)

'If Column A is blank, Reset Column variable

'and Increment Row variable

Else:

colNum = 1

rowNum = rowNum + 1

End If

Next

End Sub</pre.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Wow! The code worked beautifully as expected! Thanks a lot DerbyDad03!

Is there any way of doing that without a code?

Sure: Manually Copy...PasteSpecial...Transpose each set of cells between the blank rows.

The problem with coming up with a formula is that the data sets do not all contain the same number of rows. There's no formula that can determine where each data set begins and ends. VBA can do that, but not a formula.

If each group of cells was the same number of rows, then a formula could be used.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Ask Your Question

Weekly Poll

Do you think Monopoly should update its pieces?

Discuss in The Lounge

Poll History