Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
find first empty cell in column and start transpose next row in th
There are two articles available in VBA help that you should probably read.
One is "Using ActiveX Controls in Documents" and the other is "Using ActiveX Controls in Sheets". To access these articles, open VBA help and type "Using ActiveX Controls" in the search box. Afer you have read the articales, maybe you can be more specific about what you want to do. "ali" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
find first empty cell in column and start transpose next row in th
Sorry about that Ali, wrong thread.
"ali" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find first empty cell in column | Excel Discussion (Misc queries) | |||
Find first empty cell in column | Excel Discussion (Misc queries) | |||
How to: Find first empty cell in column | Excel Worksheet Functions | |||
find first empty cell in column and start transpose next row in that cell | Excel Discussion (Misc queries) | |||
Find First empty cell at end of Column A | Excel Programming |