Cut Row and Paste as Column (Excel 2000 or XP)

Topic

New Reply

Does anyone know a way to cut an entire row and paste it as a column in an Excel Worksheet?

Reply | Quote

Replies

Does this do what you want?
Steve

Option Explicit
Sub TransposeRowToColumn()
    Dim rngS As Range
    Dim rngD As Range
    Dim vArray
    Dim i As Integer
    Set rngS = Application.InputBox("What is the Source Range", Type:=8)
    Set rngD = Application.InputBox("What is the Destination", Type:=8)
    Set rngD = rngD.Cells(1, 1)
    vArray = rngS
    rngS.ClearContents
    
    For i = 1 To UBound(vArray, 2)
        rngD.Offset(i - 1, 0) = vArray(1, i)
    Next
End Sub

Reply | Quote

Yes, this does what I want. Thank you!!!!

Reply | Quote

Yes, this does what I want. Thank you!!!!

Reply | Quote

Does this do what you want?
Steve

Option Explicit
Sub TransposeRowToColumn()
    Dim rngS As Range
    Dim rngD As Range
    Dim vArray
    Dim i As Integer
    Set rngS = Application.InputBox("What is the Source Range", Type:=8)
    Set rngD = Application.InputBox("What is the Destination", Type:=8)
    Set rngD = rngD.Cells(1, 1)
    vArray = rngS
    rngS.ClearContents
    
    For i = 1 To UBound(vArray, 2)
        rngD.Offset(i - 1, 0) = vArray(1, i)
    Next
End Sub

Reply | Quote

You can’t do that directly. You either have to use VBA like Steve showed you, or do it in a three step process. Select the data to be moved and do Edit/Copy. Select the cell in the column where you want the data to start. “Select PasteSpecial…”, tick Transpose at the bottom of the dialog box, and click OK. Now, delete the original data.

Reply | Quote

You can’t do that directly. You either have to use VBA like Steve showed you, or do it in a three step process. Select the data to be moved and do Edit/Copy. Select the cell in the column where you want the data to start. “Select PasteSpecial…”, tick Transpose at the bottom of the dialog box, and click OK. Now, delete the original data.

Reply | Quote