Solved

Excel copy/paste and transpose question

Posted on 2006-06-08
2
332 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SignTool Error with Windows 10 5 122
Cannot change default program for *.txt anymore 5 78
Salesforce Campaign - 4,500 members 5 65
Christmas family picture with greetings 19 83
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
In this article, you will read about the trends across the human resources departments for the upcoming year. Some of them include improving employee experience, adopting new technologies, using HR software to its full extent, and integrating artifi…
This video demonstrates how to use each tool, their shortcuts, where and when to use them, and how to use the keyboard to improve workflow.
Video by: Tony
This video teaches viewers how to export a project from Adobe Premiere Pro and the various file types involved.

867 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now