I created the code below by the example you gave.
Change the Worksheets(1) and Worksheets(2) according to the worksheet with the origin and the worksheet that will serve as the destination.
Public Sub Replica()
Dim linha As Range
Dim Celula As Range
Dim Atributos As String
Dim Metricas As String
NumeroLinha = 1
For Each linha In Worksheets(1).UsedRange.Rows
Atributos = ""
Metricas = ""
For Each Celula In linha.Cells
If TypeName(Celula.Value) = "String" And Celula.Value <> "" Then
Atributos = IIf(Atributos = "", Celula.Value, Atributos & ";" & Celula.Value)
Else
Metricas = IIf(Metricas = "", Celula.Value, Metricas & ";" & Celula.Value)
End If
Next Celula
AtributosArray = Split(Atributos, ";")
MetricasArray = Split(Metricas, ";")
For i = 0 To UBound(MetricasArray)
Worksheets(2).Rows(NumeroLinha).Resize(, UBound(AtributosArray) + 1).FormulaArray = AtributosArray
Worksheets(2).Cells(NumeroLinha, UBound(AtributosArray) + 2) = MetricasArray(i)
NumeroLinha = NumeroLinha + 1
Next i
Next linha
End Sub
Perfect, but it’s a gigantic spreadsheet! , the way it is already has 10k lines, when transposing , will have 1 line q will turn 30 lines or even more, so I’m after something more automated!
– Matheus Gonzales
Just create the macro I posted in the other reply.
– Roger Mello