Excel VBA：将列转换为行 [英] Excel VBA: Transpose a column to a row

问题描述

我需要将我的列转置到一行。我有一个代码，我发现它的作品，但它并不完全符合我的期望。

这是我的文件，我运行代码之前：

这是我运行代码后我的文件的样子：

我希望我的结果显示在列顶部单元格的行上。在这种情况下，我希望将列转置在第5行，而不是像第二张照片那样跳到第1行。

这是我的代码： / p>

  Private Sub CommandButton1_Click（）
 Dim rng As Range 
 Dim I As Long 
 
设置rng =范围（B5）
虽然rng.Value<> 
 I = I + 1 
 rng.Resize（60）.Copy 
 Range（C& I）.PasteSpecial Transpose：= True 
 Set rng = rng .Offset（60）
 
 Wend 
 rng.EntireColumn.Delete 
 End Sub 
  

解决方案

一种方式：

 '//源列范围
设置rng =范围（B5，范围（B5）。结束（xlDown））
 
'//从目标单元格调整大小，转置
范围（ B5）。调整大小（rng.Columns.Count，rng.Rows.Count）.Value = WorksheetFunction.Transpose（rng）
 
'//清除转置值
'// offset by 1保存B5 
 rng.Offset（1，0）.Clear 
  

I need to transpose my Column to a Row. I have a code I found that works but it is not doing exactly what I desire.

This is what my file looks like before I run the code:

This is what my file looks like after I run the code:

I want my result to be displayed on the row that the top cell of the column is on. In this case, I want the column to transpose on the 5th row instead of jumping up to the 1st like you see in the second picture.

This is my Code:

Private Sub CommandButton1_Click()
Dim rng As Range
Dim I As Long

    Set rng = Range("B5")
    While rng.Value <> ""
        I = I + 1
        rng.Resize(60).Copy
        Range("C" & I).PasteSpecial Transpose:=True
        Set rng = rng.Offset(60)

    Wend
    rng.EntireColumn.Delete
End Sub

解决方案

One way:

'// source column range
Set rng = Range("B5", Range("B5").End(xlDown))

'// resize from destination cell, transpose
Range("B5").Resize(rng.Columns.Count, rng.Rows.Count).Value = WorksheetFunction.Transpose(rng)

'// clear transposed values
'// offset by 1 to preserve B5
rng.Offset(1, 0).Clear

这篇关于Excel VBA：将列转换为行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！