根据逗号分隔的列表插入行 [英] Insert rows based on comma-separated list
本文介绍了根据逗号分隔的列表插入行的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
一列中的某些单元格包含一个以上的项目,并用逗号分隔.
Some cells in a column contain more that one item separated by commas.
我要为所有项目都排一行.
I want a row for all the items.
这里是一个例子:
原始:
应该:
推荐答案
正如jswolf19所提到的,您可以使用 SPLIT
函数将定界的字符串转换为数组.然后,只需遍历数组中的项目并根据需要插入新行.
As jswolf19 mentions, you can use the SPLIT
function to turn a delimited string in to an array. Then, simply iterate over the items in the array and insert new rows as necessary.
下面的步骤应该使您入门.
The procedure below should get you started.
我假设您的数据在A:E列中,并使用 rng
变量进行设置.根据需要进行修改.
I assume your data is in columns A:E, and set this using the rng
variable. Modify that as needed.
根据OP注释修改的代码
Sub SplitPartsRows()
Dim rng As Range
Dim r As Long
Dim arrParts() As String
Dim partNum As Long
'## In my example i use columns A:E, and column D contains the Corresponding Parts ##
Set rng = Range("A1:BI13876") '## Modify as needed ##'
r = 2
Do While r <= rng.Rows.Count
'## Split the value in column BB (54) by commas, store in array ##
arrParts = Split(rng(r, 54).Value, ",")
'## If there's more than one item in the array, add new lines ##
If UBound(arrParts) >= 1 Then '## corrected this logic for base 0 array
rng(r, 54).Value = arrParts(0)
'## Iterate over the items in the array ##
For partNum = 1 To UBound(arrParts)
'## Insert a new row ##'
'## increment the row counter variable ##
r = r + 1
rng.Rows(r).Insert Shift:=xlDown
'## Copy the row above ##'
rng.Rows(r).Value = rng.Rows(r - 1).Value
'## update the part number in the new row ##'
rng(r, 54).Value = Trim(arrParts(partNum))
'## resize our range variable as needed ##
Set rng = rng.Resize(rng.Rows.Count + 1, rng.Columns.Count)
Next
End If
'## increment the row counter variable ##
r = r + 1
Loop
End Sub
这篇关于根据逗号分隔的列表插入行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文