EXCEL VBA 帮助 - 插入空白单元格的功能
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2973752/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
EXCEL VBA help - function to insert blank cell
提问by Kaskade
I need some help with an excel VBA function.
我需要一些有关 excel VBA 函数的帮助。
I have data that looks like this
我有看起来像这样的数据
ColA ColB
a123 a123
a124 a124
a127 a126
a128 a127
.... ....
I want to compare the contents of ColA and ColB, Where the contents are different I want to insert a blank cell into column A. So the result will look like:
我想比较 ColA 和 ColB 的内容，内容不同的地方我想在 A 列中插入一个空白单元格。所以结果将如下所示：
ColA ColB
a123 a123
a124 a124
a126
a127 a127
.... ....
Any suggestions as to how I could do this in Excel.
关于如何在 Excel 中执行此操作的任何建议。
Thanks in advance
提前致谢
UPDATED
更新
I tried the method below with the following code to insert the cell and it is working fine, I now realize when I run it that I need some more functionality though.
我用下面的代码尝试了下面的方法来插入单元格，它工作正常，我现在意识到当我运行它时我需要更多的功能。
first_col.Cells(row, 1).Select
Selection.Insert Shift:=xlDown
if the value in ColA with the "a" removed is less than the value in ColB with the "a" removed I want to insert the cell in in ColA and I also need to insert a cell in the same position in ColC (contains other data). If ColB has a larger value I want to insert the cell in ColB only. I think I know what to do inside the If statement but I'm not sure how to construct the IF. Here is what I am thinking
如果删除了“a”的 ColA 中的值小于删除了“a”的 ColB 中的值我想在 ColA 中插入单元格，我还需要在 ColC 中的相同位置插入一个单元格（包含其他数据）。如果 ColB 具有更大的值，我只想在 ColB 中插入单元格。我想我知道在 If 语句中该做什么，但我不确定如何构造 IF。这是我的想法
Set other_col = Range("C1:C100")
//if substring of ColA > substring of ColB
first_col.Cells(row, 1).Select
Selection.Insert Shift:=xlDown
other_col.Cells(row, 1).Select
Selection.Insert Shift:=xlDown
//else
second_col.Cells(row, 1).Select
Selection.Insert Shift:=xlDown
采纳答案by Michael
Your comparison statement could look something like this:
您的比较语句可能如下所示：
Sub Expand()
Dim first_col As Range, cell As Range
Dim row As Integer
Set first_col = Range("A2:A100")
For Each cell In first_col
If Right(cell.Value, 3) < Right(cell.Offset(0, 1).Value, 3) Then
' Shift in here
End If
Next cell
End Sub
The "Right" function looks at the number of characters from the right as specified by the second parameter.
“Right”函数从第二个参数指定的右边开始查看字符数。
One other note is that code sections like this that use the .select method:
另一个注意事项是像这样使用 .select 方法的代码部分：
first_col.Cells(row, 1).Select
Selection.Insert Shift:=xlDown
Can be truncated like this:
可以这样截断：
first_col.cells(row, 1).insert shift:=xlDown
The "select" and "selection" are leftovers from the macro recorder and usually aren't used in VBA code.
“选择”和“选择”是宏记录器的剩余部分，通常不在 VBA 代码中使用。
回答by e.James
I don't know the exact VBA code to insert a row, but you can find that by recording a macro. Here is the rest of the code (to loop through the columns and do the comparison):
我不知道插入行的确切 VBA 代码，但您可以通过录制宏来找到。这是其余的代码（循环遍历列并进行比较）：
Sub Expand()
Dim first_col as Range
Dim second_col as Range
Dim row as Integer
Set first_col = Range("A2:A100")
Set second_col = Range("B2:B100")
For row = 1 To second_col.Rows.Count
If first_col.Cells(Row, 1).Value <> second_col.Cells(Row, 1).Value Then
'// code to insert the row'
End If
Next row
End Sub
回答by Husam Khiry
i record a macro by selecting some cells and insert and i tried by creating my own sub and it s work
我通过选择一些单元格并插入来记录一个宏，我尝试通过创建自己的子项和它的工作
Range("c16:e16").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
edit note this code cause unmerg to merged cells
编辑注意此代码导致取消合并合并单元格
