Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old July 21st 07, 01:45 PM posted to microsoft.public.excel.misc
ali ali is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2007
Posts: 22
Default find first empty cell in column and start transpose next row in that cell

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


  #2   Report Post  
Old July 21st 07, 02:18 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 10,124
Default find first empty cell in column and start transpose next row in that cell

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


  #3   Report Post  
Old July 21st 07, 08:48 PM posted to microsoft.public.excel.misc
ali ali is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2007
Posts: 22
Default find first empty cell in column and start transpose next row in that cell

On Jul 21, 2:18 pm, "Don Guillett" wrote:
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- Hide quoted text -


- 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.

  #4   Report Post  
Old July 21st 07, 09:28 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 10,124
Default find first empty cell in column and start transpose next row in that cell

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

"ali" wrote in message
ups.com...
On Jul 21, 2:18 pm, "Don Guillett" wrote:
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- Hide quoted text -


- 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.


  #5   Report Post  
Old July 21st 07, 10:21 PM posted to microsoft.public.excel.misc
ali ali is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2007
Posts: 22
Default find first empty cell in column and start transpose next row in that cell

On Jul 21, 9:28 pm, "Don Guillett" wrote:
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
"ali" wrote in message

ups.com...



On Jul 21, 2:18 pm, "Don Guillett" wrote:
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


groups.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- Hide quoted text -


- 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.- Hide quoted text -


- Show quoted text -


Thanks Don.

I have emailed the book to u.

ali



  #6   Report Post  
Old July 21st 07, 11:14 PM posted to microsoft.public.excel.misc
ali ali is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2007
Posts: 22
Default find first empty cell in column and start transpose next row in that cell

On Jul 21, 10:21 pm, ali wrote:
On Jul 21, 9:28 pm, "Don Guillett" wrote:





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
"ali" wrote in message


oups.com...


On Jul 21, 2:18 pm, "Don Guillett" wrote:
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


groups.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- Hide quoted text -


- 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.- Hide quoted text -


- Show quoted text -


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

  #7   Report Post  
Old July 21st 07, 11:55 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 10,124
Default find first empty cell in column and start transpose next row in that cell


Please TOP post here \
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"ali" wrote in message
oups.com...
On Jul 21, 10:21 pm, ali wrote:
On Jul 21, 9:28 pm, "Don Guillett" wrote:





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
"ali"
wrote in message


oups.com...


On Jul 21, 2:18 pm, "Don Guillett" wrote:
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


groups.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- Hide quoted text -


- 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.- Hide quoted text -


- Show quoted text -


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




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Autofilling next empty cell in column? Bruce New Users to Excel 2 April 21st 06 04:51 AM
find last none empty cell kevcar40 Excel Discussion (Misc queries) 3 March 1st 06 11:59 AM
Copy from row above if cell is empty in column Jacky D. Excel Discussion (Misc queries) 2 December 20th 05 07:36 PM
Find an empty cell and put a dash in it? markexcel Excel Worksheet Functions 5 October 12th 05 03:47 PM
Find a empty cell in next column Michael Excel Discussion (Misc queries) 3 June 15th 05 02:18 PM


All times are GMT +1. The time now is 09:41 PM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017