find first empty cell in column and start transpose next row in that cell

[ali]

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

 

[Don Guillett]

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

 

[ali]

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










- Show quoted text -

Dear Don,

Could u please help me a bit further with it.

your code fixes the number of elements to be transpsoed from columns
other than d. If "d" has 20 values and "e" has 30 values then only 20
values from "e" will be transposed. Can u kindly make it flexible for
all the columns.

Thanks a lot.

 

[Don Guillett]

Another thing we do here is TOP post.
The example you gave showed
row1: 1 2 blank 0 3
I put that data starting in col D so there would be d,e,f,g,etc on THAT row.
So, i don't understand your question?
You should learn to ask the question that you want the answer to.
If desired, send me a workbook with DETAILED desires.

 

[ali]

Another thing we do here is TOP post.
The example you gave showed
row1: 1 2 blank 0 3
I put that data starting in col D so there would be d,e,f,g,etc on THAT row.
So, i don't understand your question?
You should learn to ask the question that you want the answer to.
If desired, send me a workbook with DETAILED desires.
--
Don Guillett
Microsoft MVP Excel
SalesAid Software









- Show quoted text -

Thanks Don.

I have emailed the book to u.

ali

 

[ali]

Thanks Don.

I have emailed the book to u.

ali- Hide quoted text -

- Show quoted text -

Thanks Don. Problem sorted. I was misreading the code.

ali

 

[Don Guillett]

Please TOP post here \