Macro Help

[Guest]

I'm using Office 98

I have written a macro to take rows of data and make it columns of data. I
am doing this one group at a time. This is the macro I am using.
Keyboard Shortcut: Ctrl+q
'
Selection.Copy
Range("A13500").End(xlUp).Offset(1, 0).Select

Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone,
SkipBlanks:=False _
, Transpose:=True
End Sub

My issue is that I have several rows of data, with a blank space inbetween
each section; rather than doing it one section at a time, is there a way to
edit the macro to read a blank space and make the next section of data occur
in a new row.

Example: Currently I take
Name
Address
Phone

with my macro it does a transpose, to make it look like this
Name Address Phone

I do this one section at a time. If I have the following:
Name1
Address1
Phone 1
<blank line>
Name 2
Address2
Phone 2

Can I make it look like this
Name 1 Address 1 Phone 1
Name 2 Address 2 Phone 2

without having to do each section individually?

Thank you.

 

[Bernie Deitrick]

BDK,

Use the macro below. Select the cells first, then run it. Assumes that the
blanks are really blank.

HTH,
Bernie
MS Excel MVP

Sub TryNow()
Dim myArea As Range

For Each myArea In Selection.SpecialCells(xlCellTypeConstants).Areas
myArea.Copy
Range("A13500").End(xlUp).Offset(1, 0) _
.PasteSpecial Paste:=xlAll, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=True
Next myArea

End Sub

 

[Gord Dibben]

BDK

If data is consistent as per your example.

Public Sub AddressSort()
last = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count
With Worksheets(1)
For x = last To 2 Step -1
If .Cells(x, 1).Value <> "" And _
Cells(x - 1, 1).Value <> "" Then
Range(.Cells(x, 1), .Cells(x, 10)).Copy _
Destination:=.Range(.Cells(x - 1, 2), .Cells(x - 1, 2))
Rows(x).Delete
End If
Next x
Range("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End With
End Sub


Gord Dibben Excel MVP