Skip blanks

Hello,

I have a Macro which uses paste special. I want to skip blank cells and
transpose the data. Transposing the data works fine but it doesn't skip the
blank cells. Here's the relevant portion of my Macro:

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
Transpose:=True

Any idea what I'm doing wrong?

Thank you,

Art.

Try
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=True, Transpose:=True
Regards,
Alan.
"Art MacNeil" <artmacneil@shaw.ca> wrote in message
news:6RYxf.215225$2k.138418@pd7tw1no...
> Hello,
>
> I have a Macro which uses paste special. I want to skip blank cells and
> transpose the data. Transposing the data works fine but it doesn't skip
> the blank cells. Here's the relevant portion of my Macro:
>
> Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
> Transpose:=True
>
> Any idea what I'm doing wrong?
>
> Thank you,
>
> Art.
>

My bad. I originally copied the incorrect line from my Macro. I should have
posted: Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=True, Transpose:=True and it doesn't skip blanks.


Again, any help would be greatly appreciated,

Art.



"Alan" <alan111@ntlworld.com> wrote in message
news:%23aoOLNLGGHA.1032@TK2MSFTNGP15.phx.gbl...
> Try
> Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks
> _
> :=True, Transpose:=True
> Regards,
> Alan.
> "Art MacNeil" <artmacneil@shaw.ca> wrote in message
> news:6RYxf.215225$2k.138418@pd7tw1no...
>> Hello,
>>
>> I have a Macro which uses paste special. I want to skip blank cells and
>> transpose the data. Transposing the data works fine but it doesn't skip
>> the blank cells. Here's the relevant portion of my Macro:
>>
>> Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
>> Transpose:=True
>>
>> Any idea what I'm doing wrong?
>>
>> Thank you,
>>
>> Art.
>>
>
>

On Sat, 14 Jan 2006 03:24:36 GMT, "Art MacNeil" <artmacneil@shaw.ca> wrote:

>My bad. I originally copied the incorrect line from my Macro. I should have
>posted: Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
>SkipBlanks _
> :=True, Transpose:=True and it doesn't skip blanks.
>
>
>Again, any help would be greatly appreciated,
>
>Art.

It seems to work OK here.

Perhaps you are misunderstanding the function of the Skip Blanks option?

That option prevents blank cells that are being pasted into a new range, from
overwriting (blanking out) data that is already in that destination range.


--ron

"Ron Rosenfeld" <ronrosenfeld@nospam.org> wrote in message
news:d5tgs19kthdj4d19cmn69quq6o5ffpcloa@4ax.com...
> On Sat, 14 Jan 2006 03:24:36 GMT, "Art MacNeil" <artmacneil@shaw.ca>
> wrote:
>
>>My bad. I originally copied the incorrect line from my Macro. I should
>>have
>>posted: Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
>>SkipBlanks _
>> :=True, Transpose:=True and it doesn't skip blanks.
>>
>>
>>Again, any help would be greatly appreciated,
>>
>>Art.
>
> It seems to work OK here.
>
> Perhaps you are misunderstanding the function of the Skip Blanks option?
>
> That option prevents blank cells that are being pasted into a new range,
> from
> overwriting (blanking out) data that is already in that destination range.
>
>
> --ron



Indeed I am. Is there a way to paste data and ignore any blank cells?

Thank you,

Art.

On Sat, 14 Jan 2006 04:48:16 GMT, "Art MacNeil" <artmacneil@shaw.ca> wrote:

>>
>> It seems to work OK here.
>>
>> Perhaps you are misunderstanding the function of the Skip Blanks option?
>>
>> That option prevents blank cells that are being pasted into a new range,
>> from
>> overwriting (blanking out) data that is already in that destination range.
>>
>>
>> --ron
>
>
>
>Indeed I am. Is there a way to paste data and ignore any blank cells?
>
>Thank you,
>
>Art.
>

Yes. Depends on the type of data you have and what, exactly, you need.

If you are dealing with all constants, or all formulas, you could select just
the non-blank cells:

Selection.SpecialCells(xlCellTypeConstants, 23).Copy

If it won't cause a problem with other data, you could delete the blank cells,
before or after copying:

Again, the precise code would depend on your layout, but something like:

Selection.SpecialCells(xlCellTypeBlanks).Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft

You'd have to figure out the original destination range to use this.

Something like (if the original selection is a single column):

======================
Sub foo()
Dim AOI As Range
Dim Dest As Range
Dim AOISize As Long

Set AOI = Selection
Set Dest = [D1]
AOISize = AOI.Rows.Count

AOI.Copy

Dest.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True

Set Dest = Range(Dest, Dest.Offset(0, AOISize - 1).Address)

Dest.SpecialCells(xlCellTypeBlanks).Delete (xlToLeft)
Application.CutCopyMode = False

Dest.Cells(1, 1).Select

End Sub
===========================


--ron

"Ron Rosenfeld" <ronrosenfeld@nospam.org> wrote in message
news:v2phs1pqpq3m967b8qvghd15cbsg5r30mb@4ax.com...
> On Sat, 14 Jan 2006 04:48:16 GMT, "Art MacNeil" <artmacneil@shaw.ca>
> wrote:
>
>>>
>>> It seems to work OK here.
>>>
>>> Perhaps you are misunderstanding the function of the Skip Blanks option?
>>>
>>> That option prevents blank cells that are being pasted into a new range,
>>> from
>>> overwriting (blanking out) data that is already in that destination
>>> range.
>>>
>>>
>>> --ron
>>
>>
>>
>>Indeed I am. Is there a way to paste data and ignore any blank cells?
>>
>>Thank you,
>>
>>Art.
>>
>
> Yes. Depends on the type of data you have and what, exactly, you need.
>
> If you are dealing with all constants, or all formulas, you could select
> just
> the non-blank cells:
>
> Selection.SpecialCells(xlCellTypeConstants, 23).Copy
>
> If it won't cause a problem with other data, you could delete the blank
> cells,
> before or after copying:
>
> Again, the precise code would depend on your layout, but something like:
>
> Selection.SpecialCells(xlCellTypeBlanks).Select
> Application.CutCopyMode = False
> Selection.Delete Shift:=xlToLeft
>
> You'd have to figure out the original destination range to use this.
>
> Something like (if the original selection is a single column):
>
> ======================
> Sub foo()
> Dim AOI As Range
> Dim Dest As Range
> Dim AOISize As Long
>
> Set AOI = Selection
> Set Dest = [D1]
> AOISize = AOI.Rows.Count
>
> AOI.Copy
>
> Dest.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
> False, Transpose:=True
>
> Set Dest = Range(Dest, Dest.Offset(0, AOISize - 1).Address)
>
> Dest.SpecialCells(xlCellTypeBlanks).Delete (xlToLeft)
> Application.CutCopyMode = False
>
> Dest.Cells(1, 1).Select
>
> End Sub
> ===========================
>
>
> --ron

Thanks Ron,

I'll give that a try.

Art.