Macro to transpose selected cells

sanz · Jul 22, 2011

Hi,

I want a macro for selecting No of cell in Column "A" and Paste transposing it in Column "C"

Eg:- Select "1,2,3,8" cells from column A and Transpose it in Column "C"

 <table class="MsoNormalTable" style="width: 240pt; margin-left: 4.65pt; border-collapse: collapse;" border="0" cellpadding="0" cellspacing="0" width="320"> <tbody><tr style="height: 15.75pt;"> <td style="width: 48pt; border-width: 1pt 1pt medium; border-style: solid solid none; border-color: windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15.75pt;" nowrap="nowrap" valign="bottom" width="64"> Input
</td> <td colspan="4" style="width: 192pt; border-width: 1pt 1pt 1pt medium; border-style: solid solid solid none; border-color: windowtext black windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15.75pt;" nowrap="nowrap" valign="bottom" width="256"> Output
</td> </tr> <tr style="height: 15pt;"> <td style="width: 48pt; border: 1pt solid windowtext; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="64"> 1
</td> <td style="width: 48pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="64"> 1
</td> <td style="width: 48pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="64"> 2
</td> <td style="width: 48pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="64"> 3
</td> <td style="width: 48pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="64"> 8
</td> </tr> <tr style="height: 15pt;"> <td style="width: 48pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="64"> 2
</td> <td style="width: 48pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="64">
</td> <td style="width: 48pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="64">
</td> <td style="width: 48pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="64">
</td> <td style="width: 48pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="64">
</td> </tr> <tr style="height: 15pt;"> <td style="width: 48pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="64"> 3
</td> <td style="width: 48pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="64">
</td> <td style="width: 48pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="64">
</td> <td style="width: 48pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="64">
</td> <td style="width: 48pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="64">
</td> </tr> <tr style="height: 15pt;"> <td style="width: 48pt; border-width: medium 1pt; border-style: none solid; border-color: -moz-use-text-color windowtext; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="64"> 8
</td> <td style="width: 48pt; border-width: medium 1pt medium medium; border-style: none solid none none; border-color: -moz-use-text-color windowtext -moz-use-text-color -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="64">
</td> <td style="width: 48pt; border-width: medium 1pt medium medium; border-style: none solid none none; border-color: -moz-use-text-color windowtext -moz-use-text-color -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="64">
</td> <td style="width: 48pt; border-width: medium 1pt medium medium; border-style: none solid none none; border-color: -moz-use-text-color windowtext -moz-use-text-color -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="64">
</td> <td style="width: 48pt; border-width: medium 1pt medium medium; border-style: none solid none none; border-color: -moz-use-text-color windowtext -moz-use-text-color -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="64">
</td> </tr> <tr style="height: 15pt;"> <td style="width: 48pt; border-width: 1pt medium medium 1pt; border-style: solid none none solid; border-color: windowtext -moz-use-text-color -moz-use-text-color windowtext; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="64">
</td> <td style="width: 48pt; border-width: 1pt medium medium; border-style: solid none none; border-color: windowtext -moz-use-text-color -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="64">
</td> <td style="width: 48pt; border-width: 1pt medium medium; border-style: solid none none; border-color: windowtext -moz-use-text-color -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="64">
</td> <td style="width: 48pt; border-width: 1pt medium medium; border-style: solid none none; border-color: windowtext -moz-use-text-color -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="64">
</td> <td style="width: 48pt; border-width: 1pt 1pt medium medium; border-style: solid solid none none; border-color: windowtext windowtext -moz-use-text-color -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="64">
</td> </tr> <tr style="height: 15pt;"> <td style="width: 48pt; border-width: medium medium 1pt 1pt; border-style: none none solid solid; border-color: -moz-use-text-color -moz-use-text-color windowtext windowtext; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="64">
</td> <td style="width: 48pt; border-width: medium medium 1pt; border-style: none none solid; border-color: -moz-use-text-color -moz-use-text-color windowtext; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="64">
</td> <td style="width: 48pt; border-width: medium medium 1pt; border-style: none none solid; border-color: -moz-use-text-color -moz-use-text-color windowtext; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="64">
</td> <td style="width: 48pt; border-width: medium medium 1pt; border-style: none none solid; border-color: -moz-use-text-color -moz-use-text-color windowtext; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="64">
</td> <td style="width: 48pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="64">
</td> </tr> <tr style="height: 15pt;"> <td style="width: 48pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="64"> 4
</td> <td style="width: 48pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="64"> 4
</td> <td style="width: 48pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="64"> 5
</td> <td style="width: 48pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="64"> 6
</td> <td style="width: 48pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="64"> 7
</td> </tr> <tr style="height: 15pt;"> <td style="width: 48pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="64"> 5
</td> <td style="width: 48pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="64">
</td> <td style="width: 48pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="64">
</td> <td style="width: 48pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="64">
</td> <td style="width: 48pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="64">
</td> </tr> <tr style="height: 15pt;"> <td style="width: 48pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="64"> 6
</td> <td style="width: 48pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="64">
</td> <td style="width: 48pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="64">
</td> <td style="width: 48pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="64">
</td> <td style="width: 48pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="64">
</td> </tr> <tr style="height: 15.75pt;"> <td style="width: 48pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0in 5.4pt; height: 15.75pt;" nowrap="nowrap" valign="bottom" width="64"> 7
</td> <td style="width: 48pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15.75pt;" nowrap="nowrap" valign="bottom" width="64">
</td> <td style="width: 48pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15.75pt;" nowrap="nowrap" valign="bottom" width="64">
</td> <td style="width: 48pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15.75pt;" nowrap="nowrap" valign="bottom" width="64">
</td> <td style="width: 48pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15.75pt;" nowrap="nowrap" valign="bottom" width="64">
</td> </tr> </tbody></table>
Any help would be much appreciated !

GTO · Jul 22, 2011

sanz said:
Hi,

I want a macro for selecting No of cell in Column "A" and Paste transposing it in Column "C"

A certain number of rows in Col A, or to the end of data (downward)?

GTO · Jul 22, 2011

Sorry, I believe I mis-read and missed the title as well

I happen to use the right-click event, but as long as you check to ensure the active sheet is a worksheet, a regular sub would work as well.

Rich (BB code):

Option Explicit
    
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    
    If Selection.Count > 1 And Selection.Columns.Count = 1 And Selection.Count <= Rows(1).Cells.Count - 2 Then
    
        If MsgBox("Would you like to transpose the selected partial column?", _
                  vbYesNo Or vbQuestion, _
                  vbNullString _
                  ) = vbYes Then
            
            Cancel = True
            Selection(1).Offset(, 2).Resize(, Selection.Count).Value = Application.Transpose(Selection.Value)
        End If
    End If
End Sub

Hope that helps,

Mark

GlennUK · Jul 22, 2011

Are you aware that you can do this without a macro ( Copy the input range, select the target cell and do Edit/Paste Special/Transpose )?

wigi · Jul 22, 2011

Duplicate thread, I already wrote code for it here:
http://www.mrexcel.com/forum/showthread.php?t=566200

sanz · Jul 22, 2011

GlennUK said:
Are you aware that you can do this without a macro ( Copy the input range, select the target cell and do Edit/Paste Special/Transpose )?

Yes, but i want to use transpose next to the given column
ie.

<table class="MsoNormalTable" border="0" cellpadding="0" cellspacing="0" width="320"><tbody><tr style="height: 15.75pt;"><td style="width: 48pt; border-width: 1pt 1pt medium; border-style: solid solid none; border-color: windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15.75pt;" nowrap="nowrap" valign="bottom" width="64">Input
</td> <td colspan="3" style="width: 192pt; border-width: 1pt 1pt 1pt medium; border-style: solid solid solid none; border-color: windowtext black windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15.75pt;" nowrap="nowrap" valign="bottom" width="256"> Output
</td> </tr> <tr style="height: 15pt;"> <td style="width: 48pt; border: 1pt solid windowtext; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="64"> 1
</td> <td style="width: 48pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="64"> 1
</td> <td style="width: 48pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="64"> 2
</td> <td style="width: 48pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="64"> 3
</td> </tr> <tr style="height: 15pt;"> <td style="width: 48pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="64"> 2
</td> <td style="width: 48pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="64">
</td> <td style="width: 48pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="64">
</td> <td style="width: 48pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="64">
</td> </tr> <tr style="height: 15pt;"> <td style="width: 48pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="64"> 3
</td> <td style="width: 48pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="64">
</td> <td style="width: 48pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="64">
</td> <td style="width: 48pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="64">
</td> </tr> </tbody></table>

sanz · Jul 22, 2011

I want to use this in loop for column "A", where every 6cells are transposed to next column.
"
Range("A1:A6").Select
Selection.Copy
Range("C1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("A7:A12").Select
Application.CutCopyMode = False
Selection.Copy
Range("C7").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
"

GTO · Jul 25, 2011

Presuming you want to use the Selection to indicate the range to be transposed, try:

Option Explicit Sub exa() Dim _ rngData As Range, _ lRowsCount As Long, _ lRemainder As Long, _ lSectionCount As Long, _ lCurrentRow As Long, _ i As Long If ActiveSheet.Type = &HFFFFEFB9 Then If Selection.Count > 1 _ And Selection.Columns.Count = 1 _ And Selection.Count <= Rows(1).Cells.Count - 2 _ And Selection.Column = 1 Then Set rngData = Selection lRowsCount = rngData.Rows.Count lRemainder = lRowsCount Mod 6 lSectionCount = lRowsCount \ 6 If lSectionCount > 0 Then lCurrentRow = rngData.Rows(1).Row For i = 1 To lSectionCount Cells(lCurrentRow, 1).Offset(, 2).Resize(, 6).Value _ = Application.Transpose(Cells(lCurrentRow, 1).Resize(6).Value) lCurrentRow = lCurrentRow + 6 Next End If If lRemainder > 0 Then Cells(lCurrentRow, 1).Offset(, 2).Resize(, lRemainder).Value _ = Application.Transpose(Cells(lCurrentRow, 1).Resize(lRemainder).Value) End If End If End If End Sub

Hope that helps,

Mark

Macro to transpose selected cells

sanz

Board Regular

Excel Facts

GTO

MrExcel MVP

GTO

MrExcel MVP

GlennUK

Well-known Member

wigi

Well-known Member

sanz

Board Regular

sanz

Board Regular

GTO

MrExcel MVP

Similar threads

Forum statistics

Share this page

Macro to transpose selected cells

Board Regular

Excel Facts

MrExcel MVP

MrExcel MVP

Well-known Member

Well-known Member

Board Regular

Board Regular

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock