Solved

Excel copy/paste and transpose question

Posted on 2006-06-08
2
337 Views
Last Modified: 2006-11-18
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?
0
Comment
Question by:bdietz
  • 2
2 Comments
 
LVL 81

Accepted Solution

by:
byundt earned 500 total points
ID: 16866093
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
 
LVL 81

Expert Comment

by:byundt
ID: 16874588
bdietz,
Thanks for the grade!
Brad
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Software to compare two files and flag if they are different 2 64
Java JRE greater than 1.6 5 72
Password Management 4 44
CD Rom not playing 13 55
Storage devices are generally used to save the data or sometime transfer the data from one computer system to another system. However, sometimes user accidentally erased their important data from the Storage devices. Users have to know how data reco…
A high-level exploration of how our ever-increasing access to information has changed the way we do our jobs.
The viewer will learn how to set up a document for the web and print and the recommended PPI for printing.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question