| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Skip Blank Cells While Copying A List | Excel Worksheet Functions | |||
| how do I skip blank cells when writing formulas | Excel Worksheet Functions | |||
| Skip blank cells in diagrams | Charts and Charting in Excel | |||
| How do I skip blank cells when copying over a range of cells? | Excel Discussion (Misc queries) | |||
| how to skip the blank cells | Excel Discussion (Misc queries) | |||
Linear Mode
