Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Excel copy/paste and transpose question

Posted on 2006-06-08
2
Medium Priority
?
342 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 2000 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

Curious about the latest ransomware attack? Check out our timeline of events surrounding the spread of this new virus along with tips on how to mitigate the damage.
One-stop solution for Exchange Administrators to address all MS Exchange Server issues, which is known by the name of Stellar Exchange Toolkit.
This video Micro Tutorial shows how to password-protect PDF files with free software. Many software products can do this, such as Adobe Acrobat (but not Adobe Reader), Nuance PaperPort, and Nuance Power PDF, but they are not free products. This vide…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

721 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