One way. Assumes data starts in col D and Col C is clear. Then delete
columns D

Sub transposerowstoonecolumn()
For I = 2 To Cells(Rows.Count, "d").End(xlUp).Row
colcount = Cells(I, Columns.Count).End(xlToLeft).Column
dlr = Cells(Rows.Count, "c").End(xlUp).Row + 1
Range(Cells(I, "d"), Cells(I, colcount)).Copy
Cells(dlr, "c").PasteSpecial Paste:=xlPasteAll, Transpose:=True
Next I
For I = Cells(Rows.Count, "c").End(xlUp).Row To 2 Step -1
If Cells(I, "c") = 0 Or Len(Trim(Cells(I, "c"))) < 1 Then
Cells(I, "c").Delete shift:=xlUp
End If
Next I
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"ali" wrote in message
oups.com...
Hi guys,

I have the following code to transpose one row into column without
blanks and zero's.

Dim lastcol As Long
Dim ws As Worksheet
Set ws = Worksheets("sheet10")
lastcol = ws.Cells(17, Columns.Count).End(xlToLeft).Column
With ws
.Range(Cells(17, 1), Cells(17, lastcol)).Replace 0, ""
.Range(.Cells(17, 1), .Cells(17,
lastcol)).SpecialCells(xlCellTypeConstants).Copy
.Range("A150").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=True
End With
Application.CutCopyMode = False


I want to be able to transpose many rows into the same column and in
that column I want no empty cells.

row1: 1 2 blank 0 3
row2: 22 33 0 44

after transpose it will look as following:

1

2

3

22

33

44