Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Changing Columns to Rows Question
I am looking for a macro that will transpose a long column of data (records seperated by a blank cell in the column) into seperate rows (each row representing a speperate record). -- unknowndevice ------------------------------------------------------------------------ unknowndevice's Profile: http://www.excelforum.com/member.php...o&userid=26646 View this thread: http://www.excelforum.com/showthread...hreadid=181991 |
#2
|
|||
|
|||
Those blank cells are really empty??? They're not just formulas that evaluate
to ""? And are those cells in column A all values (constants) or all formulas or a mixture of the two? If those blank cells are really empty and all the cells are constants, then this should work: Option Explicit Sub testme() Dim BigRange As Range Dim SmallArea As Range With ActiveSheet Set BigRange = Nothing On Error Resume Next Set BigRange = .Range("a:a").Cells.SpecialCells(xlCellTypeConstan ts) On Error GoTo 0 If BigRange Is Nothing Then MsgBox "no constants in this column!" Exit Sub End If For Each SmallArea In BigRange.Areas SmallArea.Copy SmallArea.Cells(1).Offset(0, 1).PasteSpecial Transpose:=True Next SmallArea On Error Resume Next .Range("b:b").Cells.SpecialCells(xlCellTypeBlanks) .EntireRow.Delete On Error GoTo 0 .Range("a:a").Delete End With End Sub There's no error checking to see if the number of cells exceeds the number of columns--any chance that could happen? unknowndevice wrote: I am looking for a macro that will transpose a long column of data (records seperated by a blank cell in the column) into seperate rows (each row representing a speperate record). -- unknowndevice ------------------------------------------------------------------------ unknowndevice's Profile: http://www.excelforum.com/member.php...o&userid=26646 View this thread: http://www.excelforum.com/showthread...hreadid=181991 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
changing column's order | Charts and Charting in Excel | |||
Inserting Rows w/formulas question | Excel Worksheet Functions | |||
Excel - columns into rows | Excel Discussion (Misc queries) | |||
Excel - Columns into rows | New Users to Excel | |||
Reduce columns and rows count? | Excel Worksheet Functions |