transpose skip zeros(0's)

[ali]

Hi

I am trying to transpose a row into column. I want the column to take
only non-zero and non-blank values,e.g.,

1 blank 2 0 blank 3 4

after transpose

1
2
3
4

I am using the following code:

Sub transpose_data()
Dim lastcol As Long
Dim ws As Worksheet
Set ws = Worksheets("sheet2")


lastcol = ws.Cells(1, Columns.Count).End(xlToLeft).Column
With ws
..Range(.Cells(1, 1), .Cells(1,
lastcol)).SpecialCells(xlCellTypeConstants).Copy


..Range("A2").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=True
End With
Application.CutCopyMode = False


End Sub


can somebody guide me how can i add the functionality of transposing
non-zero values only.

Thanks

ali

 

[Rick Rothstein \(MVP - VB\)]

It looks like your code works except that it keeps the 0 cell. Try adding
this line immediately after the With statement and see if it works for
you....

..Range(.Cells(1, 1), .Cells(1, lastcol)).Replace 0, ""

Rick

 

[Dave Peterson]

Check your other post, too.

Hi

I am trying to transpose a row into column. I want the column to take
only non-zero and non-blank values,e.g.,

1 blank 2 0 blank 3 4

after transpose

1
2
3
4

I am using the following code:

Sub transpose_data()
Dim lastcol As Long
Dim ws As Worksheet
Set ws = Worksheets("sheet2")

lastcol = ws.Cells(1, Columns.Count).End(xlToLeft).Column
With ws
.Range(.Cells(1, 1), .Cells(1,
lastcol)).SpecialCells(xlCellTypeConstants).Copy

.Range("A2").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=True
End With
Application.CutCopyMode = False

End Sub

can somebody guide me how can i add the functionality of transposing
non-zero values only.

Thanks

ali