Skip blank cells on transpose in a macro
 

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

Skip blank cells on transpose in a macro
 

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).D elete Shift:=xlShiftToLeft
On Error goto 0
--
Regards,
Tom Ogilvy


"Melanie O" wrote in message
...
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

Skip blank cells on transpose in a macro
 

Thanks, Tom. Worked like a charm!

Melanie

"Tom Ogilvy" wrote:

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).D elete Shift:=xlShiftToLeft
On Error goto 0
--
Regards,
Tom Ogilvy


"Melanie O" wrote in message
...
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