Excel - converting column data to rows

June 27, 2011 at 09:48:30
Specs: Windows XP
Hi Experts,

I have an Excel sheet with the following data:

A1: Name1
A2: Company1
A3: Address1
A4: Telephone1
A6: Name2
A7: Company2
A8: Address2
A9: Telephone2
A10: Fax2
A11: Email2
A13: Name3
A14: Company3
A15: Address3
A16: Email

and 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!

See More: Excel - converting column data to rows

Report •

June 27, 2011 at 17:10:29
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
colNum = 1
rowNum = rowNum + 1
End If
End Sub</pre.

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

Report •

June 27, 2011 at 22:12:52
Wow! The code worked beautifully as expected!

Thanks a lot DerbyDad03!

Report •

Report •

Related Solutions

September 2, 2011 at 01:29:21
Is there any way of doing that without a code?

Report •

September 2, 2011 at 07:26:54

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.

Report •

Ask Question