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

[junipertree]

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]

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