I need a macro to transpose multiple columns A1-Z1, A2-X2 etc
 

I have a spreadsheet that has figures in A1 to Z1, A2 to X2, A3, A5 to H5 to
A968, and I need all of these entries transposed to one column in the same
order as they are in the columns ie B2 to Go to A2, C2 to go to C2 etc
I can do this manually line by line which will take me forever.
I need a macro to copy each row and transpose this to the next available
cell in column A

I need a macro to transpose multiple columns A1-Z1, A2-X2 etc
 

Dim i As Long
Dim j As Long

Application.ScreenUpdating = False
Columns(1).Insert
j = 1
For i = 1 To 998
Cells(i, "B").Resize(1, 24).Copy
Cells(j, "A").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
Transpose:=True
j = j + 24
Next i
Application.ScreenUpdating = True


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Wombat62" wrote in message
...
I have a spreadsheet that has figures in A1 to Z1, A2 to X2, A3, A5 to H5
to
A968, and I need all of these entries transposed to one column in the same
order as they are in the columns ie B2 to Go to A2, C2 to go to C2 etc
I can do this manually line by line which will take me forever.
I need a macro to copy each row and transpose this to the next available
cell in column A

I need a macro to transpose multiple columns A1-Z1, A2-X2 etc
 

Thank Bob,

I have tried this but to no avail as this moves the parts over to start in
column B1 to AB1 instead of pasteing down the column

"Wombat62" wrote:

I have a spreadsheet that has figures in A1 to Z1, A2 to X2, A3, A5 to H5 to
A968, and I need all of these entries transposed to one column in the same
order as they are in the columns ie B2 to Go to A2, C2 to go to C2 etc
I can do this manually line by line which will take me forever.
I need a macro to copy each row and transpose this to the next available
cell in column A

I need a macro to transpose multiple columns A1-Z1, A2-X2 etc
 

No it doesn't.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Wombat62" wrote in message
...
Thank Bob,

I have tried this but to no avail as this moves the parts over to start in
column B1 to AB1 instead of pasteing down the column

"Wombat62" wrote:

I have a spreadsheet that has figures in A1 to Z1, A2 to X2, A3, A5 to
H5 to
A968, and I need all of these entries transposed to one column in the
same
order as they are in the columns ie B2 to Go to A2, C2 to go to C2 etc
I can do this manually line by line which will take me forever.
I need a macro to copy each row and transpose this to the next available
cell in column A

I need a macro to transpose multiple columns A1-Z1, A2-X2 etc
 

Hi Bob
No it doesn't
I quite agree, your code transposes down column A as requested.

But from the OP's original description, it sounded as though there were
various "lengths" of data to be transposed as row 1 was A to Z, row 2
was A to X, row 3 was just A etc.

Maybe amending your code to count the columns used in each row would be
prefereable as opposed to taking a fixed
24(??) columns of data each time. (also number of rows changed to OP's
968 rather than 998)

Sub test()
Dim i As Long
Dim j As Long
Dim k As Long

Application.ScreenUpdating = False
Columns(1).Insert
j = 1
For i = 1 To 968
k = Cells(i, Columns.Count).End(xlToLeft).Column - 1
Cells(i, "B").Resize(1, k).Copy
Cells(j, "A").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
_
Transpose:=True
j = j + k
Next i
Application.ScreenUpdating = True
End Sub


--
Regards

Roger Govier


"Bob Phillips" wrote in message
...
No it doesn't.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Wombat62" wrote in message
...
Thank Bob,

I have tried this but to no avail as this moves the parts over to
start in
column B1 to AB1 instead of pasteing down the column

"Wombat62" wrote:

I have a spreadsheet that has figures in A1 to Z1, A2 to X2, A3, A5
to
H5 to
A968, and I need all of these entries transposed to one column in
the
same
order as they are in the columns ie B2 to Go to A2, C2 to go to C2
etc
I can do this manually line by line which will take me forever.
I need a macro to copy each row and transpose this to the next
available
cell in column A

I need a macro to transpose multiple columns A1-Z1, A2-X2 etc
 

To quote the OP

.... this moves the parts over to start in column B1 to AB1 instead of
pasteing down the column ...

which is what I disagree with.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Roger Govier" wrote in message
...
Hi Bob
No it doesn't
I quite agree, your code transposes down column A as requested.

But from the OP's original description, it sounded as though there were
various "lengths" of data to be transposed as row 1 was A to Z, row 2
was A to X, row 3 was just A etc.

Maybe amending your code to count the columns used in each row would be
prefereable as opposed to taking a fixed
24(??) columns of data each time. (also number of rows changed to OP's
968 rather than 998)

Sub test()
Dim i As Long
Dim j As Long
Dim k As Long

Application.ScreenUpdating = False
Columns(1).Insert
j = 1
For i = 1 To 968
k = Cells(i, Columns.Count).End(xlToLeft).Column - 1
Cells(i, "B").Resize(1, k).Copy
Cells(j, "A").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
_
Transpose:=True
j = j + k
Next i
Application.ScreenUpdating = True
End Sub


--
Regards

Roger Govier


"Bob Phillips" wrote in message
...
No it doesn't.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Wombat62" wrote in message
...
Thank Bob,

I have tried this but to no avail as this moves the parts over to
start in
column B1 to AB1 instead of pasteing down the column

"Wombat62" wrote:

I have a spreadsheet that has figures in A1 to Z1, A2 to X2, A3, A5
to
H5 to
A968, and I need all of these entries transposed to one column in
the
same
order as they are in the columns ie B2 to Go to A2, C2 to go to C2
etc
I can do this manually line by line which will take me forever.
I need a macro to copy each row and transpose this to the next
available
cell in column A

I need a macro to transpose multiple columns A1-Z1, A2-X2 etc
 

Hi Bob

I was agreeing with your disagreeing, not disagreeing with your
disagreeing, and not agreeing with the OP's disagreeing <vbg
That's the problem with double unaries<g

The rest was just a suggestion to deal with what looks like variable
lengths of data being transposed in a fixed manner - but perhaps I have
got that wrong as well!!!

--
Regards

Roger Govier


"Bob Phillips" wrote in message
...
To quote the OP

... this moves the parts over to start in column B1 to AB1 instead of
pasteing down the column ...

which is what I disagree with.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Roger Govier" wrote in message
...
Hi Bob
No it doesn't
I quite agree, your code transposes down column A as requested.

But from the OP's original description, it sounded as though there
were
various "lengths" of data to be transposed as row 1 was A to Z, row 2
was A to X, row 3 was just A etc.

Maybe amending your code to count the columns used in each row would
be
prefereable as opposed to taking a fixed
24(??) columns of data each time. (also number of rows changed to
OP's
968 rather than 998)

Sub test()
Dim i As Long
Dim j As Long
Dim k As Long

Application.ScreenUpdating = False
Columns(1).Insert
j = 1
For i = 1 To 968
k = Cells(i, Columns.Count).End(xlToLeft).Column - 1
Cells(i, "B").Resize(1, k).Copy
Cells(j, "A").PasteSpecial Paste:=xlPasteAll,
Operation:=xlNone,
_
Transpose:=True
j = j + k
Next i
Application.ScreenUpdating = True
End Sub


--
Regards

Roger Govier


"Bob Phillips" wrote in message
...
No it doesn't.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Wombat62" wrote in message
...
Thank Bob,

I have tried this but to no avail as this moves the parts over to
start in
column B1 to AB1 instead of pasteing down the column

"Wombat62" wrote:

I have a spreadsheet that has figures in A1 to Z1, A2 to X2, A3,
A5
to
H5 to
A968, and I need all of these entries transposed to one column
in
the
same
order as they are in the columns ie B2 to Go to A2, C2 to go to
C2
etc
I can do this manually line by line which will take me forever.
I need a macro to copy each row and transpose this to the next
available
cell in column A

I need a macro to transpose multiple columns A1-Z1, A2-X2 etc
 

Thanks guy's for all of your help. You were correct the first time.

The problem was that I was still seeing the old data still in the rows as
well as being shown down column A. As the 1st two entries only held 2 columns
of data I found that there was a large gap between this and the next lot of
data which I have been able to amend.

"Roger Govier" wrote:

Hi Bob

I was agreeing with your disagreeing, not disagreeing with your
disagreeing, and not agreeing with the OP's disagreeing <vbg
That's the problem with double unaries<g

The rest was just a suggestion to deal with what looks like variable
lengths of data being transposed in a fixed manner - but perhaps I have
got that wrong as well!!!

--
Regards

Roger Govier


"Bob Phillips" wrote in message
...
To quote the OP

... this moves the parts over to start in column B1 to AB1 instead of
pasteing down the column ...

which is what I disagree with.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Roger Govier" wrote in message
...
Hi Bob
No it doesn't
I quite agree, your code transposes down column A as requested.

But from the OP's original description, it sounded as though there
were
various "lengths" of data to be transposed as row 1 was A to Z, row 2
was A to X, row 3 was just A etc.

Maybe amending your code to count the columns used in each row would
be
prefereable as opposed to taking a fixed
24(??) columns of data each time. (also number of rows changed to
OP's
968 rather than 998)

Sub test()
Dim i As Long
Dim j As Long
Dim k As Long

Application.ScreenUpdating = False
Columns(1).Insert
j = 1
For i = 1 To 968
k = Cells(i, Columns.Count).End(xlToLeft).Column - 1
Cells(i, "B").Resize(1, k).Copy
Cells(j, "A").PasteSpecial Paste:=xlPasteAll,
Operation:=xlNone,
_
Transpose:=True
j = j + k
Next i
Application.ScreenUpdating = True
End Sub


--
Regards

Roger Govier


"Bob Phillips" wrote in message
...
No it doesn't.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Wombat62" wrote in message
...
Thank Bob,

I have tried this but to no avail as this moves the parts over to
start in
column B1 to AB1 instead of pasteing down the column

"Wombat62" wrote:

I have a spreadsheet that has figures in A1 to Z1, A2 to X2, A3,
A5
to
H5 to
A968, and I need all of these entries transposed to one column
in
the
same
order as they are in the columns ie B2 to Go to A2, C2 to go to
C2
etc
I can do this manually line by line which will take me forever.
I need a macro to copy each row and transpose this to the next
available
cell in column A