Excel copy/paste and transpose question

Hi

I have a 20x20 grid of values, but i will make it 2x2 for simplicity, something like:

1 2
3 4

i can paste special to get something like:

1
2

but i want to be able to highlight all four values and just paste them like:

1
2
3
4

and obviously do this for my larger 20x20 block.  can i do this in excel?
bdietzAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
byundtConnect With a Mentor Commented:
Hi bdietz,
You can use a macro to facilitate your copying and pasting into a single column. Here is one that assumes you have already selected the cells to copy. When you run the macro, it will ask you for the destination. It will then put each row of the source data into the destination. Note: values will be copied--not formats or formulas!

Sub TransposeToOneColumn()
Dim rg As Range
Dim X As Variant, Y As Variant
Dim i As Long, j As Long, k As Long, nCols As Long, nRows As Long
X = Selection.Value
On Error Resume Next
Set rg = Application.InputBox("Please pick the top left cell where you want the results pasted", Type:=8)
On Error GoTo 0
If rg Is Nothing Then Exit Sub

nRows = UBound(X)
nCols = UBound(X, 2)
ReDim Y(1 To nRows * nCols, 1 To 1)
For i = 1 To nRows
    For j = 1 To nCols
        k = k + 1
        Y(k, 1) = X(i, j)
    Next
Next
rg.Cells(1, 1).Resize(nRows * nCols, 1).Value = Y
End Sub

To install a sub in a regular module sheet:
1) ALT + F11 to open the VBA Editor
2) Use the Insert...Module menu item to create a blank module sheet
3) Paste the suggested code in this module sheet
4) ALT + F11 to return to the spreadsheet

To run a sub or macro:
5) ALT + F8 to open the macro window
6) Select the macro
7) Click the "Run" button

Optional steps to assign a shortcut key to your macro:
8) Repeat steps 5 & 6, then press the "Options" button
9) Enter the character you want to use (Shift + character will have fewer conflicts with existing shortcuts)
10) Enter some descriptive text telling what the macro does in the "Description" field
11) Click the "OK" button

If the above procedure doesn't work, then you need to change your macro security setting. To do so, open the Tools...Macro...Security menu item. Choose Medium, then click OK.

Hoping to be helpful,

Brad
0
 
byundtCommented:
bdietz,
Thanks for the grade!
Brad
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.