Excel Transpose function limitation issue

[harleythecav]

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

 

[alansidman]

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

Power Query Transpose Data - Google Search

 

[Rick Rothstein]

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.

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]

Appreciate this information, thank you, I will look into these options.