我正在开发一个 Excel VBA 项目。我的 excel 文件中有这样的数据(每个单元格中都有每个字母)。我有很多这样的行,每行的前 6 个单元格中肯定会有数据,之后每个单元格的数量可变,为 2 个。
abcdefghij
克尔姆诺普斯特
我想将其转换为这样:
a b c d e f
g h
i j
k l m n o p
q r
s t
我该如何转置这些值?
答案1
这个小宏将从 Sheet1 中获取数据并以重新组织的方式将其放置在 Sheet2 上:
Sub DataReOrganizer()
Dim s1 As Worksheet, s2 As Worksheet
Set s1 = Sheets("Sheet1")
Set s2 = Sheets("Sheet2")
Dim r1 As Long, r2 As Long, c1 As Long
Dim N1 As Long
N1 = Cells(Rows.Count, 1).End(xlUp).Row
r1 = 1
r2 = 1
c1 = 7
For i = 1 To N1
s1.Range("A" & r1 & ":F" & r1).Copy s2.Range("A" & r2)
r2 = r2 + 1
While s1.Cells(r1, c1) <> ""
s1.Range(s1.Cells(r1, c1), s1.Cells(r1, c1 + 1)).Copy s2.Range("G" & r2)
c1 = c1 + 2
r2 = r2 + 1
Wend
r1 = r1 + 1
c1 = 7
Next i
End Sub