gpt4 book ai didi

vba - Excel 宏 - 逗号分隔的单元格到行

转载 作者:行者123 更新时间:2023-12-02 07:42:46 27 4
gpt4 key购买 nike

我在 Excel 中有以下数据:

a, b, c
d
e
f, g
h
i

每一行代表一行且在一个单元格中。

我想将其转换为:

a
b
c
d
e
f
g
h
i

我正在使用以下宏,但我无法使用自动调整大小来执行插入,而不是覆盖单元格值。如有任何帮助,我们将不胜感激。

    Sub SplitCells()


Dim i As Long



With Application

.Calculation = xlCalculationManual

.ScreenUpdating = False




For i = 1 To Selection.Rows.Count

Dim splitValues As Variant


splitValues = split(Selection.Rows(i).Value, ",")

Selection.Rows(i).Resize(UBound(splitValues) - LBound(splitValues) + 1).Value = Application.Transpose(splitValues)

Next i



.Calculation = xlCalculationAutomatic

.ScreenUpdating = True

End With

End Sub

最佳答案

这个宏将从 A 列中获取数据并将其“提取”到 B 列。结果如下所示,请放心对我的图形演示技巧感到畏惧:-)

    <- A ->   <- B ->
1 a, b, c a
2 d b
3 e c
4 f, g d
5 h e
6 i f
7 g
8 h
9 i

出于测试目的,我将其保留为非破坏性,并且由于在 VBA 中创建新列、填充它并删除旧列相对容易。给读者的练习...

这是宏:

Option Explicit
Sub Macro1()
Dim fromCol As String
Dim toCol As String
Dim fromRow As String
Dim toRow As String
Dim inVal As String
Dim outVal As String
Dim commaPos As Integer

' Copy from column A to column B.'
fromCol = "A"
toCol = "B"
fromRow = "1"
toRow = "1"

' Go until no more entries in column A.'
inVal = Range(fromCol + fromRow).Value
While inVal <> ""

' Go until all sub-entries used up.'
While inVal <> ""
Range(fromCol + fromRow).Select

' Extract each subentry.'
commaPos = InStr(1, inVal, ",")
While commaPos <> 0

' and write to output column.'
outVal = Left(inVal, commaPos - 1)
Range(toCol + toRow).Select
Range(toCol + toRow).Value = outVal
toRow = Mid(Str(Val(toRow) + 1), 2)

' Remove that sub-entry.'
inVal = Mid(inVal, commaPos + 1)
While Left(inVal, 1) = " "
inVal = Mid(inVal, 2)
Wend
commaPos = InStr(1, inVal, ",")
Wend

' Get last sub-entry (or full entry if no commas).'
Range(toCol + toRow).Select
Range(toCol + toRow).Value = inVal
toRow = Mid(Str(Val(toRow) + 1), 2)
inVal = ""
Wend

' Advance to next source row.'
fromRow = Mid(Str(Val(fromRow) + 1), 2)
Range(fromCol + fromRow).Select
inVal = Range(fromCol + fromRow).Value
Wend
End Sub

关于vba - Excel 宏 - 逗号分隔的单元格到行,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/471435/

27 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com