Pass data from a single column to several rows

Question

Pass data from a single column to several rows

Navigation

#1 by (0 votes)

0

Good afternoon, I'm trying to make a code so that taking all the data of a column, I convert them into several rows, that is, in the column I have Name1, lastname1, telephone1, Name2, lastname2, telephone2, ... and I want each record to show me in a row:

Sub make_matrix()
    Dim Arr_1()
    Dim lnumRows As Long, lnumCols As Long
    Dim rngDestRange As Range, rngCellDest As Range
    Dim i As Long, j As Long, x As Long
    Dim lCounter As Long


    If Selection.Count < 4 Then
        MsgBox "Debe seleccionar un rango de por lo menos cuatro celdas", vbExclamation
        Exit Sub
    End If

    lnumRows = Application.InputBox("Cuantas filas en la matriz?", "Filas", , , , , , 2)
    lnumCols = Application.InputBox("Cuantas columnas en la matriz?", "Columnas", , , , , , 2)

    ReDim Arr_1(1 To lnumCols, 1 To lnumRows)

    Set rngCellDest = Application.InputBox("Posicion de la primera celda de la matriz", "Copiar matriz", , , , , , 8)
    Set rngDestRange = rngCellDest.Range(Cells(1, 1), Cells(lnumCols, lnumRows))

    lCounter = 0

    For j = 1 To lnumRows
        For i = 1 To lnumCols
            Arr_1(j, i) = Selection.Item(lCounter + 1)
            lCounter = lCounter + 1
        Next i
    Next j

    rngDestRange.Value = WorksheetFunction.Transpose(Arr_1)


End Sub

vba

asked by Vieira 03.11.2017 в 16:36

source

1 answer

Help with a map made with arrangements and classes c ++ Problem with a while loop

score 0 · Answer 1

The steps would be the following:

Select and copy the range to Transpose

Select target range and paste in a special way


    Sub Transponer()
    '
    ' Transponer Macro
    '
    '
        Range("A1:A10").Select
        Selection.Copy
        Range("B1").Select
        Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
            False, Transpose:=True
    End Sub

From this basic code you can add parameters and messages at your convenience.

Considerations: * The vba code may vary slightly depending on the excel version.