Good morning!
Next, I have a csv as follows
A; B; C; D; 1; 2; 3; 4; 5
E; F; G; H; 6; 7; 8; 9; 10
The columns of letters have a regular structure, and those of a number not but it could explode them and put them in columns but it will not be able to create the desired metric
But when transposing in my imagination it would look like this:
A; B; C; D; 1
A; B; C; D; 2
A; B; C; D; 3
and so on!
Does anyone know how to do this?
abs
Try to wear the special necklace. Copy the cells you want and press Ctrl + Alt + V, if you want to transpose check the option to transpose cells.
I created the code below for the example you gave.
Change Worksheets (1) and Worksheets (2) according to the worksheet with the source and the worksheet that will serve as the target.
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 solution
Sub CONVERTROWSTOCOL_Oeldere_revisted_new()
Dim rsht1 As Long, rsht2 As Long, i As Long, col As Long, wsTest As Worksheet, mr As Worksheet, ms As Worksheet
'check if sheet "ouput" already exist
Const strSheetName As String = "Output"
Set wsTest = Nothing
On Error Resume Next
Set wsTest = ActiveWorkbook.Worksheets(strSheetName)
On Error GoTo 0
If wsTest Is Nothing Then
Worksheets.Add.Name = strSheetName
End If
'set the data
Set mr = Sheets("sheet1") 'this is the name of the source sheet
Set ms = Sheets("Output") 'this is the name of the destiny sheet
col = 2
'End set the data
With ms
.UsedRange.ClearContents
.Range("A1:B1").Value = Array("Mat", "value")
End With
rsht2 = ms.Range("A" & Rows.Count).End(xlUp).Row
With mr
rsht1 = .Range("A" & .Rows.Count).End(xlUp).Row
For i = 2 To rsht1
Do While .Cells(1, col).Value <> "" 'And .Cells(I, col).Value <> ""
rsht2 = rsht2 + 1
ms.Range("A" & rsht2).Value = .Range("A" & i).Value
ms.Range("B" & rsht2).Value = .Cells(i, col).Value
col = col + 1
Loop
col = 2
Next
End With
With ms
.Columns("A:Z").EntireColumn.AutoFit
End With
End Sub
I advise you to use this macro which I did, it will be done automatically, just create the macro and execute. If the line is not in A1, just change the location of the line within the macro.
Sub Transpor()
'Transpor Linha
contalinha = Cells(Rows.Count, 1).End(xlUp).Row
Set BigArray = Cells(1, 1).Resize(contalinha, 1)
Cells(1, 2).Resize(1, contalinha).Value = WorksheetFunction.Transpose(BigArray)
BigArray.Clear
End Sub