Skip blanks


A

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.
 
Ad

Advertisements

A

Alan

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

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.
 
R

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
 
A

Art MacNeil

Ron Rosenfeld said:
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.
 
R

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
 
Ad

Advertisements

A

Art MacNeil

Ron Rosenfeld said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top