New Questions about Recording Macros

[Greegan]

Thanks in advance...

This is what I have

I have alot of data in column A
Basically they're addresses of clients
The problem is they are all bunched together in column A
The recipient information could have a range of 5 to 10 lines of data


This is what I want to do...

I want to be able to record a macro so that whatever group of cells I
choose, I want to be able to copy, select the next cell above the data and
paste special, then transpose.

For example..

Cell A1 is blank
Cells A2 through A6 have data
Cells A7 and A8 are blank
Cells A9 through A15 have data

I want to select Cells A2 through A6, copy, select A1, paste special and
traspose the data.

The problem is that when I record this it says I specifically select A1. I
don't want to specify any one cell, but the cell above the data so that it
can be transposed.

I've included the recorded macro below.

The whole idea behind this is that we get these client lists all the time
but they can be in text or word.doc files or xls format.
Even with the Replace features we do sometimes go through hundreds of client
addresses one by one, removing hard returns manually. I am trying to find a
faster way to organize the lists.
Which leads me to enter a manual line break (such as in the special replace
option in MS WORD)
With this I make sure there is a line or two between customers and past the
text into excel hoping I can use a macro below.

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 07/12/2004 by Greegan
'
' Keyboard Shortcut: Ctrl+a
'
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True
End Sub

 

[Greegan]

Hey man that's exactly what I needed. Thank you very much.

G

 

[Dave Peterson]

Try this against a copy of your worksheet:

Option Explicit
Sub testme()

Dim myRng As Range
Dim myArea As Range

With Worksheets("sheet1")
Set myRng = Nothing
On Error Resume Next
Set myRng = .Range("a:a").Cells.SpecialCells(xlCellTypeConstants)
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "No Values in this worksheet!"
Exit Sub
End If

For Each myArea In myRng.Areas
myArea.Copy
myArea.Cells(1).Offset(0, 1).PasteSpecial Transpose:=True
Next myArea

On Error Resume Next
.Range("b:b").Cells.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
.Columns(1).Delete
On Error GoTo 0
End With

End Sub


It actually pastes the transposed data into column B, then cleans up column B
and column A.