Skip blank cells on transpose in a macro

[Guest]

I am using the following code to copy a column of data and paste
special/transpose into a new workbook:

Windows("Survey.xls").Activate
Range("B7:B21").Select
Selection.Copy
Windows("Book1").Activate
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:=False, Transpose:=True

The data in the column on the first workbook looks like this:

Name
<blank>
Address
<blank>
Phone
<blank>
etc.

Is there a way to not paste the blank cells into the new workbook? Changing
the SkipBlanks:= to True didn't seem to work.

Thanks,
Melanie

 

[Tom Ogilvy]

Windows("Survey.xls").Activate
Range("B7:B21").Select
Selection.Copy
Windows("Book1").Activate
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:=False, Transpose:=True
On Error Resume Next
selection(1).Resize(1,15).specialCells(xlBlanks).Delete Shift:=xlShiftToLeft
On Error goto 0

 

[Guest]

Thanks, Tom. Worked like a charm!

Melanie

Windows("Survey.xls").Activate
Range("B7:B21").Select
Selection.Copy
Windows("Book1").Activate
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:=False, Transpose:=True
On Error Resume Next
selection(1).Resize(1,15).specialCells(xlBlanks).Delete Shift:=xlShiftToLeft
On Error goto 0