Transpose

  • Thread starter Thread starter Joe
  • Start date Start date
J

Joe

Hi

I have 3394 number verticaly from A1 to A3394. I would like to have these
numbers horizontally say in groups of 16( B1:B16, C1:C16, etc). I know
transpose can do this put this will have to be in some sort of macro so that
I do not have to Copy an Transpose every group of numbers.

Is there anyway of doing this.

Thanks in adance

JK
 
Hi Joe

It's not clear to me, what you mean.
Using transpose in groups of 16 would
place group 1 (A1:A16) in B1:Q1, group 2
(A17:A32) in B2:Q2, etc.??
 
Hi
in B1 enter the formula
=OFFSET($A$1,ROW()-1+(COLUMN()-2)*16,0)
and copy down/to the right. Afterwards copy the data range and insert
it again with 'Edit - Paste Special - Values' to remove the formulas
 
Hi,

The following macro does the trick. It assumes that your numbers star
from A1. Newly arranged numbers start from B2:Q2, B3:Q3, ... Afte
that, you can remove the column A.

This macro is made with XL2003, so there may be some arguments i
PasteSpecial-operation that gives an error message in earlie
Excel-versions. Normally in that case, you can just delete th
argument.


Sub TransposeInGroups()
Dim lRow As Long

lRow = 1

With Application.ActiveSheet

While .Cells(lRow, 1) <> ""

.Range(.Cells(lRow, 1), .Cells(lRow + 15, 1)).Copy

.Range(.Cells(Int(lRow / 16) + 1, 2), .Cells(Int(lRow / 16) + 1
2)).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone
SkipBlanks:=False, Transpose:=True

lRow = lRow + 16

Wend

End With

End Sub

- Asse
 
Joe

Sub ColtoRows()
Dim rng As Range
Dim i As Long
Dim j As Long
Set rng = Cells(Rows.Count, 1).End(xlUp)
j = 1
On Error GoTo endit
nocols = InputBox("Enter Number of Columns Desired")
If nocols = "" Or Not IsNumeric(nocols) Then Exit Sub
For i = 1 To rng.Row Step nocols
Cells(j, "A").Resize(1, nocols).Value = _
Application.Transpose(Cells(i, "A").Resize(nocols, 1))
j = j + 1
Next
Range(Cells(j, "A"), Cells(rng.Row, "A")).ClearContents
Exit Sub
endit:
End Sub

Copy/paste the code to a general module in your workbook. Hit ALT + F11
to get to the Visual Basic Editor. View>Project Explorer. Left-click on your
workbook/project. Insert>Module. Paste in here.

ALT + F11 to go back to Excel. Tools>Macro>Macros. Select the macro by name
then Run.

NOTE: When asked "how many columns" enter 16.

Remember also. There is no "Undo" from a macro. Make sure you try this on a
copy of the worksheet first.

Gord Dibben Excel MVP
 
Joe said:
I'm sorry your right. But I think you know what I mean.
Click to expand...

My guess would have been "transpose", but over the years many
of my guesses have been wrong, so I have stopped guessing
and instead asked the poster for a clarification (also a hint to
proof read your question before posting it)

When there are two contradicting possibilities to choose from
(transpose or putting the values in B1:B16, C1:C16 etc) and either
seems to be a meaningful choice, I'm not going to use time on
finding a solution for one of them, only to be told, that it was the
other one, you wanted :-)

LeoH
 
Back
Top