ExcelBanter - Transpose cells skipping blanks

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Transpose cells skipping blanks (https://www.excelbanter.com/excel-programming/393465-transpose-cells-skipping-blanks.html)

ali

Transpose cells skipping blanks

Hi there,

Could someone help me sort this one out.

I have data in row 1 in 5 cells. lets say

1 2 blank 3 4

now I want to write a macro in which this row is transposed without
blank cell, i.e., with 4 cells:

1

2

3

4

could someone help me please.

Gary Keramidas

Transpose cells skipping blanks

maybe something like this, just change the references:

range("A1:E1").SpecialCells(xlCellTypeConstants).c opy

range("A2").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=True

--

Gary

"ali" wrote in message
ups.com...
Hi there,

Could someone help me sort this one out.

I have data in row 1 in 5 cells. lets say

1 2 blank 3 4

now I want to write a macro in which this row is transposed without
blank cell, i.e., with 4 cells:

1

2

3

4

could someone help me please.

driller

Transpose cells skipping blanks

Hi Ali,
just for a starter solution...not quite final! Using array formula.

with ur data on A1:E1

type on A2:
{=(IF($A$1:$E$1<"",SMALL($A$1:$E$1,ROW(A2)-1),"X"))}

press Ctrl+shft+ent

then copy down to A2.

but i believe macroman can paste also formulas, kinda like that, as u wish..

regards,
driller
--
*****
birds of the same feather flock together..birdwise, it''s more worth
knowing the edges rather than focusing in one line! Know the limits and
remember the extents - dive with Jonathan Seagull

"ali" wrote:

Hi there,

Could someone help me sort this one out.

I have data in row 1 in 5 cells. lets say

1 2 blank 3 4

now I want to write a macro in which this row is transposed without
blank cell, i.e., with 4 cells:

1

2

3

4

could someone help me please.

ali

Transpose cells skipping blanks

On Jul 16, 10:41 pm, "Gary Keramidas" <GKeramidasATmsn.com wrote:
maybe something like this, just change the references:

range("A1:E1").SpecialCells(xlCellTypeConstants).c opy

range("A2").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=True

--

Gary

"ali" wrote in message

ups.com...

Hi there,

Could someone help me sort this one out.

I have data in row 1 in 5 cells. lets say

1 2 blank 3 4

now I want to write a macro in which this row is transposed without
blank cell, i.e., with 4 cells:

1

2

3

4

could someone help me please.- Hide quoted text -

- Show quoted text -

thanks gary.

Another issue is that the list is changing in size all the time.
Sometimes there are 100 elements, sometimes there are 20, etc. Can we
do it by writing a macro.

Thanks

ali

driller

Transpose cells skipping blanks

Sorry! Ali,

wrong starter from me in figuring out your DATA!
Gary has a good one...

regards,
driller
--
*****
birds of the same feather flock together..birdwise, it''s more worth
knowing the edges rather than focusing in one line! Know the limits and
remember the extents - dive with Jonathan Seagull

"driller" wrote:

Hi Ali,
just for a starter solution...not quite final! Using array formula.

with ur data on A1:E1

type on A2:
{=(IF($A$1:$E$1<"",SMALL($A$1:$E$1,ROW(A2)-1),"X"))}

press Ctrl+shft+ent

then copy down to A2.

but i believe macroman can paste also formulas, kinda like that, as u wish..

regards,
driller
--
*****
birds of the same feather flock together..birdwise, it''s more worth
knowing the edges rather than focusing in one line! Know the limits and
remember the extents - dive with Jonathan Seagull

"ali" wrote:

Hi there,

Could someone help me sort this one out.

I have data in row 1 in 5 cells. lets say

1 2 blank 3 4

now I want to write a macro in which this row is transposed without
blank cell, i.e., with 4 cells:

1

2

3

4

could someone help me please.

Gary Keramidas

Transpose cells skipping blanks

this may be what you want. you should qualify the ranges, so change the sheet
name to your sheet name.

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

--

Gary

"ali" wrote in message
ups.com...
On Jul 16, 10:41 pm, "Gary Keramidas" <GKeramidasATmsn.com wrote:
maybe something like this, just change the references:

range("A1:E1").SpecialCells(xlCellTypeConstants).c opy

range("A2").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=True

--

Gary

"ali" wrote in message

ups.com...

Hi there,

Could someone help me sort this one out.

I have data in row 1 in 5 cells. lets say

1 2 blank 3 4

now I want to write a macro in which this row is transposed without
blank cell, i.e., with 4 cells:

1

2

3

4

could someone help me please.- Hide quoted text -

- Show quoted text -

thanks gary.

Another issue is that the list is changing in size all the time.
Sometimes there are 100 elements, sometimes there are 20, etc. Can we
do it by writing a macro.

Thanks

ali

ali

Transpose cells skipping blanks

On Jul 16, 11:32 pm, "Gary Keramidas" <GKeramidasATmsn.com wrote:
this may be what you want. you should qualify the ranges, so change the sheet
name to your sheet name.

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

--

Gary

"ali" wrote in message

ups.com...

On Jul 16, 10:41 pm, "Gary Keramidas" <GKeramidasATmsn.com wrote:
maybe something like this, just change the references:

range("A1:E1").SpecialCells(xlCellTypeConstants).c opy

range("A2").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=True

--

Gary

"ali" wrote in message

roups.com...

Hi there,

Could someone help me sort this one out.

I have data in row 1 in 5 cells. lets say

1 2 blank 3 4

now I want to write a macro in which this row is transposed without
blank cell, i.e., with 4 cells:

1

2

3

4

could someone help me please.- Hide quoted text -

- Show quoted text -

thanks gary.

Another issue is that the list is changing in size all the time.
Sometimes there are 100 elements, sometimes there are 20, etc. Can we
do it by writing a macro.

Thanks

ali- Hide quoted text -

- Show quoted text -

thanks gary

this eems to be the answer. However having customized the ranges it is
giving the error "subscript out of range". Could you please have a
look at my code:

Dim lastcol As Long
Dim ws As Worksheet
Set ws = Worksheets("sheet10")

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

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

Thanks a lot

ali

Gary Keramidas

Transpose cells skipping blanks

i got the same thing until i renamed one of my sheets sheet10.

are you sure that's the exact name of the sheet?

--

Gary

"ali" wrote in message
oups.com...
On Jul 16, 11:32 pm, "Gary Keramidas" <GKeramidasATmsn.com wrote:
this may be what you want. you should qualify the ranges, so change the sheet
name to your sheet name.

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

--

Gary

"ali" wrote in message

ups.com...

On Jul 16, 10:41 pm, "Gary Keramidas" <GKeramidasATmsn.com wrote:
maybe something like this, just change the references:

range("A1:E1").SpecialCells(xlCellTypeConstants).c opy

range("A2").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=True

--

Gary

"ali" wrote in message

roups.com...

Hi there,

Could someone help me sort this one out.

I have data in row 1 in 5 cells. lets say

1 2 blank 3 4

now I want to write a macro in which this row is transposed without
blank cell, i.e., with 4 cells:

1

2

3

4

could someone help me please.- Hide quoted text -

- Show quoted text -

thanks gary.

Another issue is that the list is changing in size all the time.
Sometimes there are 100 elements, sometimes there are 20, etc. Can we
do it by writing a macro.

Thanks

ali- Hide quoted text -

- Show quoted text -

thanks gary

this eems to be the answer. However having customized the ranges it is
giving the error "subscript out of range". Could you please have a
look at my code:

Dim lastcol As Long
Dim ws As Worksheet
Set ws = Worksheets("sheet10")

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

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

Thanks a lot

ali

ali

Transpose cells skipping blanks

On Jul 17, 1:07 am, "Gary Keramidas" <GKeramidasATmsn.com wrote:
i got the same thing until i renamed one of my sheets sheet10.

are you sure that's the exact name of the sheet?

--

Gary

"ali" wrote in message

oups.com...

On Jul 16, 11:32 pm, "Gary Keramidas" <GKeramidasATmsn.com wrote:
this may be what you want. you should qualify the ranges, so change the sheet
name to your sheet name.

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

--

Gary

"ali" wrote in message

roups.com...

On Jul 16, 10:41 pm, "Gary Keramidas" <GKeramidasATmsn.com wrote:
maybe something like this, just change the references:

range("A1:E1").SpecialCells(xlCellTypeConstants).c opy

range("A2").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=True

--

Gary

"ali" wrote in message

roups.com...

Hi there,

Could someone help me sort this one out.

I have data in row 1 in 5 cells. lets say

1 2 blank 3 4

now I want to write a macro in which this row is transposed without
blank cell, i.e., with 4 cells:

1

2

3

4

could someone help me please.- Hide quoted text -

- Show quoted text -

thanks gary.

Another issue is that the list is changing in size all the time.
Sometimes there are 100 elements, sometimes there are 20, etc. Can we
do it by writing a macro.

Thanks

ali- Hide quoted text -

- Show quoted text -

thanks gary

this eems to be the answer. However having customized the ranges it is
giving the error "subscript out of range". Could you please have a
look at my code:

Dim lastcol As Long
Dim ws As Worksheet
Set ws = Worksheets("sheet10")

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

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

Thanks a lot

ali- Hide quoted text -

- Show quoted text -

dear Gary

It is so strange. It is working fine for all the sheets in the
workbook except for the one in which it needs to work. In the project
explorer the sheet is named as follows:

sheet10(sheet30)

it seems to be such a strange problem.

I don't know whether I can further be helped?

Thanks a lot anyway. Your code is great and quick.

Thanks

ali

Gary Keramidas

Transpose cells skipping blanks

use sheet30, not sheet10

it's also the name on the sheet tab

make this sheet the active sheet

in vb, press control-g to show the immediate window, if it isn't already visible

type the following in the immediate window and press enter

?activesheet.name

whatever it returns needs to go into the code on this line
Set ws = Worksheets("sheet30")

--

Gary

"ali" wrote in message
oups.com...
On Jul 17, 1:07 am, "Gary Keramidas" <GKeramidasATmsn.com wrote:
i got the same thing until i renamed one of my sheets sheet10.

are you sure that's the exact name of the sheet?

--

Gary

"ali" wrote in message

oups.com...

On Jul 16, 11:32 pm, "Gary Keramidas" <GKeramidasATmsn.com wrote:
this may be what you want. you should qualify the ranges, so change the
sheet
name to your sheet name.

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

--

Gary

"ali" wrote in message

roups.com...

On Jul 16, 10:41 pm, "Gary Keramidas" <GKeramidasATmsn.com wrote:
maybe something like this, just change the references:

range("A1:E1").SpecialCells(xlCellTypeConstants).c opy

range("A2").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=True

--

Gary

"ali" wrote in message

roups.com...

Hi there,

Could someone help me sort this one out.

I have data in row 1 in 5 cells. lets say

1 2 blank 3 4

now I want to write a macro in which this row is transposed without
blank cell, i.e., with 4 cells:

1

2

3

4

could someone help me please.- Hide quoted text -

- Show quoted text -

thanks gary.

Another issue is that the list is changing in size all the time.
Sometimes there are 100 elements, sometimes there are 20, etc. Can we
do it by writing a macro.

Thanks

ali- Hide quoted text -

- Show quoted text -

thanks gary

this eems to be the answer. However having customized the ranges it is
giving the error "subscript out of range". Could you please have a
look at my code:

Dim lastcol As Long
Dim ws As Worksheet
Set ws = Worksheets("sheet10")

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

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

Thanks a lot

ali- Hide quoted text -

- Show quoted text -

dear Gary

It is so strange. It is working fine for all the sheets in the
workbook except for the one in which it needs to work. In the project
explorer the sheet is named as follows:

sheet10(sheet30)

it seems to be such a strange problem.

I don't know whether I can further be helped?

Thanks a lot anyway. Your code is great and quick.

Thanks

ali

ali

Transpose cells skipping blanks

On Jul 17, 2:04 am, "Gary Keramidas" <GKeramidasATmsn.com wrote:
use sheet30, not sheet10

it's also the name on the sheet tab

make this sheet the active sheet

in vb, press control-g to show the immediate window, if it isn't already visible

type the following in the immediate window and press enter

?activesheet.name

whatever it returns needs to go into the code on this line
Set ws = Worksheets("sheet30")

--

Gary

"ali" wrote in message

oups.com...

On Jul 17, 1:07 am, "Gary Keramidas" <GKeramidasATmsn.com wrote:
i got the same thing until i renamed one of my sheets sheet10.

are you sure that's the exact name of the sheet?

--

Gary

"ali" wrote in message

groups.com...

On Jul 16, 11:32 pm, "Gary Keramidas" <GKeramidasATmsn.com wrote:
this may be what you want. you should qualify the ranges, so change the
sheet
name to your sheet name.

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

--

Gary

"ali" wrote in message

roups.com...

On Jul 16, 10:41 pm, "Gary Keramidas" <GKeramidasATmsn.com wrote:
maybe something like this, just change the references:

range("A1:E1").SpecialCells(xlCellTypeConstants).c opy

range("A2").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=True

--

Gary

"ali" wrote in message

roups.com...

Hi there,

Could someone help me sort this one out.

I have data in row 1 in 5 cells. lets say

1 2 blank 3 4

now I want to write a macro in which this row is transposed without
blank cell, i.e., with 4 cells:

1

2

3

4

could someone help me please.- Hide quoted text -

- Show quoted text -

thanks gary.

Another issue is that the list is changing in size all the time.
Sometimes there are 100 elements, sometimes there are 20, etc. Can we
do it by writing a macro.

Thanks

ali- Hide quoted text -

- Show quoted text -

thanks gary

this eems to be the answer. However having customized the ranges it is
giving the error "subscript out of range". Could you please have a
look at my code:

Dim lastcol As Long
Dim ws As Worksheet
Set ws = Worksheets("sheet10")

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

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

Thanks a lot

ali- Hide quoted text -

- Show quoted text -

dear Gary

It is so strange. It is working fine for all the sheets in the
workbook except for the one in which it needs to work. In the project
explorer the sheet is named as follows:

sheet10(sheet30)

it seems to be such a strange problem.

I don't know whether I can further be helped?

Thanks a lot anyway. Your code is great and quick.

Thanks

ali- Hide quoted text -

- Show quoted text -

ali

Transpose cells skipping blanks

Gary Keramidas

Transpose cells skipping blanks

if you want to, you can.
--

Gary

"ali" wrote in message
oups.com...
On Jul 17, 2:04 am, "Gary Keramidas" <GKeramidasATmsn.com wrote:
use sheet30, not sheet10

it's also the name on the sheet tab

make this sheet the active sheet

in vb, press control-g to show the immediate window, if it isn't already
visible

type the following in the immediate window and press enter

?activesheet.name

whatever it returns needs to go into the code on this line
Set ws = Worksheets("sheet30")

--

Gary

"ali" wrote in message

oups.com...

On Jul 17, 1:07 am, "Gary Keramidas" <GKeramidasATmsn.com wrote:
i got the same thing until i renamed one of my sheets sheet10.

are you sure that's the exact name of the sheet?

--

Gary

"ali" wrote in message

groups.com...

On Jul 16, 11:32 pm, "Gary Keramidas" <GKeramidasATmsn.com wrote:
this may be what you want. you should qualify the ranges, so change the
sheet
name to your sheet name.

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

--

Gary

"ali" wrote in message

roups.com...

On Jul 16, 10:41 pm, "Gary Keramidas" <GKeramidasATmsn.com wrote:
maybe something like this, just change the references:

range("A1:E1").SpecialCells(xlCellTypeConstants).c opy

range("A2").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=True

--

Gary

"ali" wrote in message

roups.com...

Hi there,

Could someone help me sort this one out.

I have data in row 1 in 5 cells. lets say

1 2 blank 3 4

now I want to write a macro in which this row is transposed
without
blank cell, i.e., with 4 cells:

1

2

3

4

could someone help me please.- Hide quoted text -

- Show quoted text -

thanks gary.

Another issue is that the list is changing in size all the time.
Sometimes there are 100 elements, sometimes there are 20, etc. Can we
do it by writing a macro.

Thanks

ali- Hide quoted text -

- Show quoted text -

thanks gary

this eems to be the answer. However having customized the ranges it is
giving the error "subscript out of range". Could you please have a
look at my code:

Dim lastcol As Long
Dim ws As Worksheet
Set ws = Worksheets("sheet10")

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

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

Thanks a lot

ali- Hide quoted text -

- Show quoted text -

dear Gary

It is so strange. It is working fine for all the sheets in the
workbook except for the one in which it needs to work. In the project
explorer the sheet is named as follows:

sheet10(sheet30)

it seems to be such a strange problem.

I don't know whether I can further be helped?

Thanks a lot anyway. Your code is great and quick.

Thanks

ali- Hide quoted text -

- Show quoted text -

hi gary thanks.

can I send the sheet to you by email so that u can check?

Thanks a lot

ali

Transpose cells skipping blanks

Srinath NS

use sheet30, not sheet10it's also the name on the sheet tabmakethis sheet the

Hi Ali & Gary,

I was reading your posts, Gary gr8 job..

I have a similar problem, can someone help me on this..
the problem is
in my excel sheet, i have data something like below:
sri
12345
asdf

gary
2345
awsfd

richard
324324
asdgd

this all data is under one column & multiple rows, i need to get them to diff columns as shown below
col A col B col C
sri 12345 asdf
gary 2345 awsdf
richard 324324 asdgd

excel's transpose functionality is working fine, but i'm having extra burden in deleting those rows again..
is there a simpler way to do this??

can anyone help me on this..

Thanks & regards
Srinath

On Monday, July 16, 2007 5:29 PM ali wrote:

Hi there,

Could someone help me sort this one out.

I have data in row 1 in 5 cells. lets say

1 2 blank 3 4

now I want to write a macro in which this row is transposed without
blank cell, i.e., with 4 cells:

1

2

3

4

could someone help me please.

On Monday, July 16, 2007 5:41 PM Gary Keramidas wrote:

maybe something like this, just change the references:

range("A1:E1").SpecialCells(xlCellTypeConstants).c opy

range("A2").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=True

--

Gary

"ali" wrote in message
ups.com...

On Monday, July 16, 2007 6:02 PM drille wrote:

Hi Ali,
just for a starter solution...not quite final! Using array formula.

with ur data on A1:E1

type on A2:
{=(IF($A$1:$E$1<"",SMALL($A$1:$E$1,ROW(A2)-1),"X"))}

press Ctrl+shft+ent

then copy down to A2.

but i believe macroman can paste also formulas, kinda like that, as u wish..

regards,
driller
--
*****
birds of the same feather flock together..birdwise, it''s more worth
knowing the edges rather than focusing in one line! Know the limits and
remember the extents - dive with Jonathan Seagull

"ali" wrote:

On Monday, July 16, 2007 6:06 PM ali wrote:

thanks gary.

Another issue is that the list is changing in size all the time.
Sometimes there are 100 elements, sometimes there are 20, etc. Can we
do it by writing a macro.

Thanks

ali

On Monday, July 16, 2007 6:24 PM drille wrote:

Sorry! Ali,

wrong starter from me in figuring out your DATA!
Gary has a good one...

regards,
driller
--
*****
birds of the same feather flock together..birdwise, it''s more worth
knowing the edges rather than focusing in one line! Know the limits and
remember the extents - dive with Jonathan Seagull

"driller" wrote:

On Monday, July 16, 2007 6:32 PM Gary Keramidas wrote:

this may be what you want. you should qualify the ranges, so change the sheet
name to your sheet name.

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

--

Gary

"ali" wrote in message
ups.com...

On Monday, July 16, 2007 7:06 PM ali wrote:

On Jul 16, 11:32 pm, "Gary Keramidas" <GKeramidasATmsn.com wrote:

thanks gary

this eems to be the answer. However having customized the ranges it is
giving the error "subscript out of range". Could you please have a
look at my code:

Dim lastcol As Long
Dim ws As Worksheet
Set ws = Worksheets("sheet10")

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

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

Thanks a lot

ali

On Monday, July 16, 2007 8:07 PM Gary Keramidas wrote:

i got the same thing until i renamed one of my sheets sheet10.

are you sure that is the exact name of the sheet?

--

Gary

On Monday, July 16, 2007 8:50 PM ali wrote:

On Jul 17, 1:07 am, "Gary Keramidas" <GKeramidasATmsn.com wrote:

dear Gary

It is so strange. It is working fine for all the sheets in the
workbook except for the one in which it needs to work. In the project
explorer the sheet is named as follows:

sheet10(sheet30)

it seems to be such a strange problem.

I don't know whether I can further be helped?

Thanks a lot anyway. Your code is great and quick.

Thanks

ali

On Monday, July 16, 2007 9:04 PM Gary Keramidas wrote:

use sheet30, not sheet10

it's also the name on the sheet tab

make this sheet the active sheet

in vb, press control-g to show the immediate window, if it isn't already visible

type the following in the immediate window and press enter

?activesheet.name

whatever it returns needs to go into the code on this line
Set ws = Worksheets("sheet30")

--

Gary

"ali" wrote in message
oups.com...

Submitted via EggHeadCafe
Microsoft ASP.NET For Beginners
http://www.eggheadcafe.com/training-...NET/7/ASP.aspx

Gord Dibben[_2_]

use sheet30, not sheet10it's also the name on the sheet tabmake this sheet the

If the data sets are separated by a blank as your example this macro will do the
trick.

The data sets don't have to be same size but must have a blank between sets.

Sub coltorows()
'transpose from one column to rows
'uneven data but a blank in between each set
LastRow = Range("A" & Rows.Count).End(xlUp).Row
rowCount = 1
colCount = 2
For LoopCount = 1 To LastRow
If Range("A" & (rowCount + 1)) = "" Then
colCount = 2
rowCount = rowCount + 1
Rows(rowCount).Delete
Else
Cells(rowCount, colCount) = Range("A" & (rowCount + 1))
colCount = colCount + 1
Rows(rowCount + 1).Delete
End If
Next LoopCount
End Sub

Gord Dibben MS Excel MVP

On Fri, 03 Dec 2010 12:45:06 -0600, Srinath NS wrote:

Hi Ali & Gary,

I was reading your posts, Gary gr8 job..

I have a similar problem, can someone help me on this..
the problem is
in my excel sheet, i have data something like below:
sri
12345
asdf

gary
2345
awsfd

richard
324324
asdgd

this all data is under one column & multiple rows, i need to get them to diff columns as shown below
col A col B col C
sri 12345 asdf
gary 2345 awsdf
richard 324324 asdgd

excel's transpose functionality is working fine, but i'm having extra burden in deleting those rows again..
is there a simpler way to do this??

can anyone help me on this..

Thanks & regards
Srinath

On Monday, July 16, 2007 5:29 PM ali wrote:

Hi there,

Could someone help me sort this one out.

I have data in row 1 in 5 cells. lets say

1 2 blank 3 4

now I want to write a macro in which this row is transposed without
blank cell, i.e., with 4 cells:

1

2

3

4

could someone help me please.

On Monday, July 16, 2007 5:41 PM Gary Keramidas wrote:

maybe something like this, just change the references:

range("A1:E1").SpecialCells(xlCellTypeConstants).c opy

range("A2").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=True

--

Gary

"ali" wrote in message
ups.com...

On Monday, July 16, 2007 6:02 PM drille wrote:

Hi Ali,
just for a starter solution...not quite final! Using array formula.

with ur data on A1:E1

type on A2:
{=(IF($A$1:$E$1<"",SMALL($A$1:$E$1,ROW(A2)-1),"X"))}

press Ctrl+shft+ent

then copy down to A2.

but i believe macroman can paste also formulas, kinda like that, as u wish..

regards,
driller
--
*****
birds of the same feather flock together..birdwise, it''s more worth
knowing the edges rather than focusing in one line! Know the limits and
remember the extents - dive with Jonathan Seagull

"ali" wrote:

On Monday, July 16, 2007 6:06 PM ali wrote:

thanks gary.

Another issue is that the list is changing in size all the time.
Sometimes there are 100 elements, sometimes there are 20, etc. Can we
do it by writing a macro.

Thanks

ali

On Monday, July 16, 2007 6:24 PM drille wrote:

Sorry! Ali,

wrong starter from me in figuring out your DATA!
Gary has a good one...

regards,
driller
--
*****
birds of the same feather flock together..birdwise, it''s more worth
knowing the edges rather than focusing in one line! Know the limits and
remember the extents - dive with Jonathan Seagull

"driller" wrote:

On Monday, July 16, 2007 6:32 PM Gary Keramidas wrote:

this may be what you want. you should qualify the ranges, so change the sheet
name to your sheet name.

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

--

Gary

"ali" wrote in message
ups.com...

On Monday, July 16, 2007 7:06 PM ali wrote:

On Jul 16, 11:32 pm, "Gary Keramidas" <GKeramidasATmsn.com wrote:

thanks gary

this eems to be the answer. However having customized the ranges it is
giving the error "subscript out of range". Could you please have a
look at my code:

Dim lastcol As Long
Dim ws As Worksheet
Set ws = Worksheets("sheet10")

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

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

Thanks a lot

ali

On Monday, July 16, 2007 8:07 PM Gary Keramidas wrote:

i got the same thing until i renamed one of my sheets sheet10.

are you sure that is the exact name of the sheet?

--

Gary

On Monday, July 16, 2007 8:50 PM ali wrote:

On Jul 17, 1:07 am, "Gary Keramidas" <GKeramidasATmsn.com wrote:

dear Gary

It is so strange. It is working fine for all the sheets in the
workbook except for the one in which it needs to work. In the project
explorer the sheet is named as follows:

sheet10(sheet30)

it seems to be such a strange problem.

I don't know whether I can further be helped?

Thanks a lot anyway. Your code is great and quick.

Thanks

ali

On Monday, July 16, 2007 9:04 PM Gary Keramidas wrote:

use sheet30, not sheet10

it's also the name on the sheet tab

make this sheet the active sheet

in vb, press control-g to show the immediate window, if it isn't already visible

type the following in the immediate window and press enter

?activesheet.name

whatever it returns needs to go into the code on this line
Set ws = Worksheets("sheet30")

--

Gary

"ali" wrote in message
oups.com...

Submitted via EggHeadCafe
Microsoft ASP.NET For Beginners
http://www.eggheadcafe.com/training-...NET/7/ASP.aspx

All times are GMT +1. The time now is 07:20 AM.