Label Format to Column Format
Saved from a previous post...
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?
drakehouse wrote:
I've tried the offset command and a loop macro (which it's been several
years, so have been unsucessful) for the following scenario:
In column A, I have several contacts pasted in address label format.
I'd like to get them in column format. Column A currently looks like:
John Doe
123 Anywhere Lane
Mountains, CA 95555
(555) 555-5555
Jane Doe
1111 Anywhere Lane
Oceans, CA 97777
(555) 555-7777
There are two blank rows between the two contacts, which is what is
hanging me up.
Thanks so much!
A
--
Dave Peterson
|