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: Name1A2: Company1A3: Address1A4: Telephone1A5: A6: Name2A7: Company2A8: Address2A9: Telephone2A10: Fax2A11: Email2A12: A13: Name3A14: Company3A15: Address3A16: 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!

See More: Excel - converting column data to rows

#1
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 Else: colNum = 1 rowNum = rowNum + 1 End If NextEnd Sub How To Post Data or Code.`

Report •

#2
June 27, 2011 at 22:12:52
 Wow! The code worked beautifully as expected!Thanks a lot DerbyDad03!

Report •

#3
June 28, 2011 at 01:43:51

Report •

Related Solutions

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

Report •

#5
September 2, 2011 at 07:26:54
 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.

Report •