• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Copy 250 array formula(reference in diffrent sheet) from horizontal to vertical


New Member
Hi Friends,

I Want to copy 250 array formula(reference in different sheet) from horizontal to vertical without changing reference point.
I want only same formula but in vertical not in horizontal.

I don't want to use Transpose function as i want to retain same array formula.

I have replace "=" with "ZZZ" in all pasted vertically(Transpose) & again changed to "ZZZ" to "=" but since it's array formula every cell i don't want to type Ctrl+Shift+Enter.

Please support. I am using MS Excel 2007




Excel Ninja
I am curious to know if you have tried approach like below. Try to run this by selecting the cells in the column.

Dim rng as Range
For each rng in Selection
    rng.FormulaArray = Replace(rng.Value,"ZZZ","=")
Next rng


Excel Ninja
You should reread Forum Rules
There are clear sentences about what should do, if someone would like to use Cross-posting.

Peter Bartholomew

Well-Known Member
Beg, borrow or steal access to Excel 365 for the task! Array operation is the default for 365 and the copy / paste transposed process achieves what you want.

If you can afford it, consider updating to Microsoft 365, In use, the process of developing solutions in 365 does not have to be remotely similar to traditional spreadsheet development, and, as someone that makes extensive use of array formulas, it may well suit you far better.