Excel Transpose Function

There is a Transpose function in Excel that lets me move a string of data from a column to a row and from a row to a column..the function looks like this:( =TRANSPOSE(A3:CW3). I think that it's called an Array

Excel won't let me change anything in an array and if I try (by mistake) to change anything I get a message and the only way out is Cntrl Alt Delete to exit Excel and start all over again.

The programs that I use that make use of arrays are at least five years old and my question is has Microsoft or anyone else found a way to change Arrays


Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ProfessorJimJamMicrosoft Excel ExpertCommented:
Yes, when Transpose is used, you cannot change part of the array.

to change that you need to refer to the original data from where the Transpose is driving out the output.  make a change in there and the reflection will be in the Transposed data.
ProfessorJimJamMicrosoft Excel ExpertCommented:
if you want to change anything inside formula then you need to first Select range in the array range and then activate Edit mode by clicking the formula in the Formula bar or pressing F2 and then make your change and then press control shift enter again.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rory ArchibaldCommented:
Just to add, editing any formula in Excel does not, and never has, require a restart. If it does for you, there's something else at work.
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Rather than using the Transpose function, you can use a code to transpose the values if your program allows that.
If so, Right Click the Sheet Tab --> View Code --> And paste the code given below in the opened code window --> Close the VBA Editor --> Save your workbook as Excel Macro-Enabled Workbook.

The following code uses the BeforeDoubleClick Event.
So after the code is placed on the sheet module as described above, you may double click the cell where you want to place the transpose values, the code will prompt you to select the First cell in the range (in this case, it would be A3), then the code will prompt you again to select the Last cell in the range (in this case it would be CW3) and as a result the code will transpose the values of the selected range and paste them below the cell which was double clicked.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim sCell As Range, eCell As Range
Dim n As Long
Dim arr As Variant
Set sCell = Application.InputBox("Select the first cell in the range.", Type:=8)
Set eCell = Application.InputBox("Select the last cell in the range", Type:=8)
n = Range(sCell, eCell).Cells.Count
arr = Range(sCell, eCell).Value
Target.Resize(n, 1).Value = Application.Transpose(arr)
Cancel = True
End Sub

Open in new window

See if this is something you can work with.
Martin LissOlder than dirtCommented:
I've requested that this question be closed as follows:

Accepted answer: 168 points for ProfessorJimJam's comment #a40861451
Assisted answer: 166 points for Rory Archibald's comment #a40861589
Assisted answer: 166 points for sktneer's comment #a40862148

for the following reason:

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.