| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Autofilling next empty cell in column? | New Users to Excel | |||
| find last none empty cell | Excel Discussion (Misc queries) | |||
| Copy from row above if cell is empty in column | Excel Discussion (Misc queries) | |||
| Find an empty cell and put a dash in it? | Excel Worksheet Functions | |||
| Find a empty cell in next column | Excel Discussion (Misc queries) | |||
Hybrid Mode
