Excel Transpose function limitation issue

harleythecav

New Member
Joined
May 18, 2020
Messages
7
Office Version
  1. 2016
Platform
  1. Windows
Hi Forum

Is there a way for the Transpose formula to return more than the first 255 values?
I seem to be hitting a limit when transposing rows to columns:
{=TRANSPOSE(A2:A258)} with only 255 values returned (even if I increase the range in the formula past A258).

Currently my workaround is starting a new block of columns with the formula having a range starting at the next row which is a bit messy.

I am looking for a formula only solution (if it exists), not VBA (as this workbook gets sent externally and not all allow macro files).

Excel version O365

Thanks
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,000
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Suggest you look at Power Query called Get & Transform located on the Data Tab in version 365. Transform is an easy task within the UI of this Excel feature. Here are some links to help you further understand this function

 
Solution

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,557
Office Version
  1. 2010
Platform
  1. Windows
I am not sure what kind values you have in your cells, but the following code successfully transposed 1000 simple text values in cells A2:A1001 to cells B2:ALM2.
VBA Code:
Sub TransposeRange()
  Dim Arr As Variant
  Arr = Range("A2", Cells(Rows.Count, "A").End(xlUp))
  Range("B2").Resize(, UBound(Arr)) = Application.Transpose(Arr)
End Sub
 

harleythecav

New Member
Joined
May 18, 2020
Messages
7
Office Version
  1. 2016
Platform
  1. Windows
Appreciate this information, thank you, I will look into these options.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,134
Messages
5,570,361
Members
412,320
Latest member
sixnine0312
Top