Is there a way to paste special --> Transpose and delete blanks?

Is there a way to paste special --> Transpose and delete blanks for a
group of data?

i.e.

A
<blank>
B
<blank>
C
<blank>

to:

A | B | C

?

I tried paste special --> transpose checked, skip blanks checked which
doesn't work..

Thanks in advance!
JuniperTree

You could always clean up after you did the pasting.

If you were doing it by hand, you could select the pasted range and then
edit|goto|special|blanks
edit|delete|shift to the left

In code:

Option Explicit
Sub testme()

Dim RngToCopy As Range
Dim DestCell As Range

With ActiveSheet
Set RngToCopy = .Range("a1:b7")
Set DestCell = .Range("C9")
End With

RngToCopy.Copy
DestCell.PasteSpecial Transpose:=True

'just in case there are no empty cells
On Error Resume Next
DestCell.Resize(RngToCopy.Columns.Count, ngToCopy.Rows.Count) _
.Cells.SpecialCells(xlCellTypeBlanks).Delete shift:=xlToLeft
On Error GoTo 0


End Sub


[email protected] wrote:
>
> Is there a way to paste special --> Transpose and delete blanks for a
> group of data?
>
> i.e.
>
> A
> <blank>
> B
> <blank>
> C
> <blank>
>
> to:
>
> A | B | C
>
> ?
>
> I tried paste special --> transpose checked, skip blanks checked which
> doesn't work..
>
> Thanks in advance!
> JuniperTree

--

Dave Peterson