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 |
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 |
All times are GMT +1. The time now is 04:37 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com