Solved

Excel copy/paste and transpose question

Posted on 2006-06-08
2
340 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

Suggested Solutions

The article will include the best Data Recovery Tools along with their Features, Capabilities, and their Download Links. Hope you’ll enjoy it and will choose the one as required by you.
All of the resources available today make learning a new digital media easier than ever-- if you know where to begin. This is a clear, simple guide to a few of the basic digital art mediums and how to begin learning them on your own.
Viewers will learn how to use the Hootsuite Dashboard.
An overview on how to enroll an hourly employee into the employee database and how to give them access into the clock in terminal.

734 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