Transpose multiple rows and columns into one column with ignoring blanks

[Cafofo]

Following from this thread: Transpose multiple rows and columns into one column with ignoring blanks

It is undoubtedly a memory problem for your machine and your excel. As I told you I have no problems with 100,000 lines and columns up to DZ.
You will have to perform executions, exit excel, to clean the memory of your machine and return to executions.

Try this

Sub Transpose_99()
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, k As Long
  Dim lr As Long, lc As Long
  '
  With Sheets("Sheet1")
    a = Range("A1:A2").Value2
    b = Range("A1:A2").Value2
    Erase a, b
    lr = .Cells.Find("*", , xlValues, , xlByRows, xlPrevious).Row
    lc = .Cells.Find("*", , xlValues, , xlByColumns, xlPrevious).Column
    a = .Range("A1", .Cells(lr, lc)).Value2
  End With
 
  ReDim b(1 To UBound(a, 1) * UBound(a, 2), 1 To 1)
  '
  For i = 1 To UBound(a, 1)
    For j = 1 To UBound(a, 2)
      If a(i, j) <> "" Then
        k = k + 1
        b(k, 1) = a(i, j)
      End If
    Next
  Next
  Sheets("Sheet2").Range("A1").Resize(k).Value = b
  Erase a, b
End Sub

Hey @DanteAmor, I was looking for something similar and find your solution. Thank you for that.

But I have only one adicional need , when transposing the whole table I need to copy the first Row of Each colum to the the side column "B" of the "Sheet2" as well.
My table has many numbers(dates) and all first rows are months. So I have to copy the Month as well( something like this )

Any idea? I tried to use hLookup but it doest not help that much

 

[Cafofo]

Just to be sure that It can be undestood, the code provided by the coleage @DanteAmor in another topic provided a solution to transpose the first table(left) to another sheet but only the entire table. I have to do something similar but I have to copy the first row for each column as well for all coresponding lines as possible to check into the image bellow.

 

[Cafofo]

Does anyone can help me with that?

 

[alansidman]

Power Query Solution

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {}, "Attribute", "Value"),
    #"Sorted Rows" = Table.Sort(#"Unpivoted Columns",{{"Attribute", Order.Ascending}})
in
    #"Sorted Rows"