本文介绍了Excel VBA:将列转换为行的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我需要将我的列转置到一行。我有一个代码,我发现它的作品,但它并不完全符合我的期望。
这是我的文件,我运行代码之前:
这是我运行代码后我的文件的样子:
我希望我的结果显示在列顶部单元格的行上。在这种情况下,我希望将列转置在第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:将列转换为行
发布评论