Skip blanks

[Art MacNeil]

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.

 

[Alan]

Try
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=True, Transpose:=True
Regards,
Alan.

 

[Art MacNeil]

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.

 

[Ron Rosenfeld]

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

 

[Art MacNeil]

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.

 

[Ron Rosenfeld]

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

 

[Art MacNeil]

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.