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

[Guest]

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

 

[Bob Phillips]

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)

 

[Guest]

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

 

[Bob Phillips]

No it doesn't.

--
HTH

Bob Phillips

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

 

[Roger Govier]

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

 

[Bob Phillips]

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]

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

 

[Guest]

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.