EXCEL VBA帮助 - 插入空白单元格的功能

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中的值小于ColB中删除了“a”的值，我想在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

3 个解决方案

#1

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.

“右”功能查看第二个参数指定的右侧字符数。

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代码。

#2

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

#3

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

编辑注意此代码导致unmerg合并单元格

#1