Maybe a macro would do it:
Maybe...
Option Explicit
Sub testme()
Dim CurWks As Worksheet
Dim NewWks As Worksheet
Dim DestCell As Range
Dim BigArea As Range
Dim SmallArea As Range
Set CurWks = Worksheets("Sheet1")
Set NewWks = Worksheets.Add
Set DestCell = NewWks.Range("A1")
With CurWks
Set BigArea = Nothing
On Error Resume Next
Set BigArea = .Columns(1).Cells.SpecialCells(xlCellTypeConstants)
On Error GoTo 0
If BigArea Is Nothing Then
MsgBox "No constants in column A"
Exit Sub
End If
For Each SmallArea In BigArea.Areas
SmallArea.Copy
DestCell.PasteSpecial Transpose:=True
Set DestCell = DestCell.Offset(1, 0)
Next SmallArea
End With
End Sub
This will not do what you want if you have any formulas in column A.
If you're new to macros:
Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html
David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm
(General, Regular and Standard modules all describe the same thing.)
