vba 确定用户是添加还是删除行

Determine whether user is adding or deleting rows

提问by thinkanotherone

I have a VBA macro that validates user entered data (I didn't use data validation/conditional formatting on purpose).

我有一个 VBA 宏来验证用户输入的数据（我没有故意使用数据验证/条件格式）。

I am using Worksheet_Changeevent to trigger the code, the problem I am facing now is, when there are row changes. I don't know whether it is a deleting / inserting rows.

我正在使用Worksheet_Change事件来触发代码，我现在面临的问题是，当有行更改时。我不知道它是否是删除/插入行。

Is there anyway to distinguish between those two?

有没有办法区分这两者？

采纳答案by brettdj

You could define a range name such as RowMarker =$A$1000

您可以定义一个范围名称，例如 RowMarker =$A$1000

Then this code on your change event will store the position of this marker against it's prior position, and report any change (then stores the new position)

然后更改事件中的此代码将存储此标记相对于其先前位置的位置，并报告任何更改（然后存储新位置）

Private Sub Worksheet_Change(ByVal Target As Range)
    Static lngRow As Long
    Dim rng1 As Range
    Set rng1 = ThisWorkbook.Names("RowMarker").RefersToRange
    If lngRow = 0 Then
    lngRow = rng1.Row
        Exit Sub
    End If
    If rng1.Row = lngRow Then Exit Sub
    If rng1.Row < lngRow Then
        MsgBox lngRow - rng1.Row & " rows removed"
    Else
        MsgBox rng1.Row - lngRow & " rows added"
    End If
    lngRow = rng1.Row
End Sub

回答by Reafidy

Try this code:

试试这个代码：

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim lNewRowCount As Long

    ActiveSheet.UsedRange
    lNewRowCount = ActiveSheet.UsedRange.Rows.Count

    If lOldRowCount = lNewRowCount Then
    ElseIf lOldRowCount > lNewRowCount Then
        MsgBox ("Row Deleted")
        lOldRowCount = lNewRowCount
    ElseIf lOldRowCount < lNewRowCount Then
        MsgBox ("Row Inserted")
        lOldRowCount = lNewRowCount
    End If

End Sub

Also add this in the ThisWorkBook module:

还要在 ThisWorkBook 模块中添加：

Private Sub Workbook_Open()
    ActiveSheet.UsedRange
    lOldRowCount = ActiveSheet.UsedRange.Rows.Count
End Sub

And then this in its own module:

然后在它自己的模块中：

Public lOldRowCount As Long

The code assumes you have data in row 1. Note the very first time you run it you make get a false result, this is because the code needs to set the lRowCount to the correct variable. Once done it should be okay from then on in.

该代码假定您在第 1 行中有数据。请注意，第一次运行它时您会得到一个错误的结果，这是因为代码需要将 lRowCount 设置为正确的变量。一旦完成，从那时起应该没问题。

If you don't want to use the Public variable and worksheet open event then you could use a named range on your worksheet somewhere and store the row count (lRowCount) there.

如果您不想使用 Public 变量和工作表打开事件，那么您可以在工作表上的某处使用命名范围并将行数 (lRowCount) 存储在那里。

回答by aevanko

Assumption: That "distinguish the two" means to distinguish adding/deleting a row from any other type of change. If you meant, how to tell if the change was an add row OR delete row, then ignore my answer below.

假设：“区分两者”意味着将添加/删除行与任何其他类型的更改区分开来。如果您的意思是，如何判断更改是添加行还是删除行，请忽略我下面的回答。

In the case of inserting or deleting a row, the target.cells.count will be all the cells in the row. So you can use this If statement to capture it. Notice I use cells.columns.count since it might be different for each file. It will also trigger if the user selects an entire row and hits "delete" (to erase the values) so you'll need to code a workaround for that, though...

在插入或删除一行的情况下，target.cells.count 将是该行中的所有单元格。因此，您可以使用此 If 语句来捕获它。请注意，我使用了 cell.columns.count，因为它可能因每个文件而异。如果用户选择一整行并点击“删除”（以擦除值），它也会触发，因此您需要为此编写解决方法，但是......

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If Target.Cells.Count = Cells.Columns.Count Then
    MsgBox "Row added or deleted"
End If

End Sub

回答by bzn

After searching for a bit decided to solve it myself. In your Worksheet module (e.g. Sheet1 under Microsoft Excel Objects in VBA Editor) insert the following:

搜索了一下后决定自己解决。在您的工作表模块（例如 VBA 编辑器中 Microsoft Excel 对象下的 Sheet1）中插入以下内容：

Private usedRowsCount As Long 'use private to limit access to var outside of sheet

'Because select occurs before change we can record the current usable row count
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  usedRowsCount = Target.Worksheet.UsedRange.rows.count 'record current row count for row event detection
End Sub

'once row count recorded at selection we can compare the used row count after change occurs
'with the Target.Address we can also detect which row has been added or removed if you need to do further mods on that row
Private Sub Worksheet_Change(ByVal Target As Range)
  If usedRowsCount < Target.Worksheet.UsedRange.rows.count Then
    Debug.Print "Row Added: ", Target.Address
  ElseIf usedRowsCount > Target.Worksheet.UsedRange.rows.count Then
    Debug.Print "Row deleted: ", Target.Address
  End If
End Sub

回答by bzn

Some of what your end purpose of distinguishing between insertions and deletions ends up as will determine how you want to proceed once an insertion or deletion has been identified. The following can probably be cut down substantially but I have tried to cover every possible scenario.

区分插入和删除的一些最终目的将决定一旦插入或删除被识别后您希望如何进行。以下内容可能会大幅减少，但我已尝试涵盖所有可能的情况。

Private Sub Worksheet_Change(ByVal Target As Range)

    On Error GoTo bm_Safe_Exit
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Dim olr As Long, nlr As Long, olc As Long, nlc As Long

    With Target.Parent.Cells
        nlc = .Find(what:=Chr(42), after:=.Cells(1), LookIn:=xlValues, lookat:=xlPart, _
                SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
        nlr = .Find(what:=Chr(42), after:=.Cells(1), LookIn:=xlValues, lookat:=xlPart, _
                SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        Application.Undo    'undo the last change event
        olc = .Find(what:=Chr(42), after:=.Cells(1), LookIn:=xlValues, lookat:=xlPart, _
                SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
        olr = .Find(what:=Chr(42), after:=.Cells(1), LookIn:=xlValues, lookat:=xlPart, _
                SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        Application.Repeat  'redo the last change event
    End With

    If nlr <> olr Or nlc <> olc Then
        Select Case nlr
            Case olr - 1
                Debug.Print "One (1) row has been deleted"
            Case Is < (olr - 1)
                Debug.Print (olr - nlr) & " rows have been deleted"
            Case olr + 1
                Debug.Print "One (1) row has been inserted"
            Case Is > (olr + 1)
                Debug.Print (nlr - olr) & " rows have been inserted"
            Case olr
                Debug.Print "No rows have been deleted or inserted"
            Case Else
                'don't know what else could happen
        End Select
        Select Case nlc
            Case olc - 1
                Debug.Print "One (1) column has been deleted"
            Case Is < (olc - 1)
                Debug.Print (olc - nlc) & " columns have been deleted"
            Case olc + 1
                Debug.Print "One (1) column has been inserted"
            Case Is > (olc + 1)
                Debug.Print (nlc - olc) & " columns have been inserted"
            Case olc
                Debug.Print "No columns have been deleted or inserted"
            Case Else
                'don't know what else could happen
        End Select
    Else
        'deal with standard Intersect(Target, Range) events here
    End If

bm_Safe_Exit:
    Application.EnableEvents = True
    Application.ScreenUpdating = True

End Sub

Essentially, this code identifies the last cell column-wise and the last cell cell row-wise. It then undoes the last operation and checks again. Comparing the two results allows it to determine whether a row/column has been inserted/deleted. Once the four measurements have been taken, it redoes the last operation so that any other more standard Worksheet_Change operations can be processed.

本质上，此代码按列标识最后一个单元格，按行标识最后一个单元格。然后它撤消上一次操作并再次检查。比较这两个结果可以确定行/列是否已被插入/删除。完成四个测量后，它会重做最后一个操作，以便可以处理任何其他更标准的 Worksheet_Change 操作。

回答by phillfri

Capture row additions and deletions in the worksheet_change event.

在 worksheet_change 事件中捕获行的添加和删除。

I create a named range called "CurRowCnt"; formula: =ROWS(Table1). Access in VBA code with:

我创建了一个名为“CurRowCnt”的命名范围；公式：=ROWS（表1）。使用 VBA 代码访问：

CurRowCnt = Evaluate(Application.Names("CurRowCnt").RefersTo)

This named range will always hold the number of rows 'after' a row(s) insertion or deletion. I find it gives a more stable CurRowCnt than using a global or module level variable, better for programming, testing and debugging.

此命名范围将始终保存插入或删除行“之后”的行数。我发现它提供了比使用全局或模块级变量更稳定的 CurRowCnt，更适合编程、测试和调试。

I save the CurRowCnt to a custom document property, again for stability purposes.

出于稳定性考虑，我将 CurRowCnt 保存到自定义文档属性。

ThisWorkbook.CustomDocumentProperties("RowCnt").Value = Evaluate(Application.Names("CurRowCnt").RefersTo)

My Worksheet_Change Event structure is as follows:

我的 Worksheet_Change 事件结构如下：

Dim CurRowCnt as Double
CurRowCnt = Evaluate(Application.Names("CurRowCnt").RefersTo)
Select Case CurRowCnt

    '' ########## ROW(S) ADDED
     Case Is > ThisWorkbook.CustomDocumentProperties("RowCnt").Value
         Dim r As Range
         Dim NewRow as Range

         ThisWorkbook.CustomDocumentProperties("RowCnt").Value = _
         Evaluate(Application.Names("CurRowCnt").RefersTo)

         For Each r In Selection.Rows.EntireRow
             Set NewRow = Intersect(Application.Range("Table1"), r)
             'Process new row(s) here
         next r

    '' ########## ROW(S) DELETED
     Case Is < ThisWorkbook.CustomDocumentProperties("RowCnt").Value

         ThisWorkbook.CustomDocumentProperties("RowCnt").Value = _
         Evaluate(Application.Names("CurRowCnt").RefersTo)

         'Process here

    '' ########## CELL CHANGE
    'Case Is = RowCnt

        'Process here

    '' ########## PROCESSING ERROR
    Case Else 'Should happen only on error with CurRowCnt or RowCnt
        'Error msg here

End Select

回答by Aleksey F.

There are two a bit another approaches both based on the following template.

有两种基于以下模板的另一种方法。

1. Define a module or class module variable of Rangetype.
2. “Pin” a special range by assigning it to the variable using absolute addressand save its address or size (it depends on approach).
3. To determine a subtype of user action manipulate with the variable in a sheet change event handler.

1. 定义类型的模块或类模块变量Range。
2. 通过使用绝对地址将其分配给变量并保存其地址或大小（取决于方法）来“固定”一个特殊范围。
3. 要确定用户操作的子类型，请使用工作表更改事件处理程序中的变量进行操作。

In the first approach the whole range of interest is assigned to the variable and range's size is saved. Then in a sheet change event handler the following cases must be processed:

在第一种方法中，将整个感兴趣的范围分配给变量并保存范围的大小。然后在工作表更改事件处理程序中必须处理以下情况：

• an exception occurs when accessing Addressproperty => the pinned range is no longer exist;
• the address of changed cell is below then pinned range => an insertion was => update the variable
• a new size of the pinned range is different from saved(smaller => something was deleted, bigger => something was inserted).

• 访问Address属性时发生异常=> 固定范围不再存在；
• 更改单元格的地址低于固定范围 => 插入 => 更新变量
• 固定范围的新大小与保存的不同（较小 => 删除了某些内容，较大 => 插入了某些内容）。

In the second approach a “marker” range is assigned to the variable (see example below) and the range address is saved in order to determine movements or shifts in any direction. Then in a sheet change event handler the following cases must be processed::

在第二种方法中，将“标记”范围分配给变量（参见下面的示例）并保存范围地址，以便确定在任何方向上的移动或偏移。然后在工作表更改事件处理程序中必须处理以下情况：

• an exception occurs when accessing Addressproperty => the pinned “marker” range is no longer exist;
• the address of changed cell is below then "marker" range => an insertion was => update the variable
• there is a difference in any direction, i.e. abs(new_row - saved_row) > 0 or abs(new_col-saved_col) > 0=> the pinned range was moved or shifted.

• 访问Address属性时发生异常=> 固定的“标记”范围不再存在；
• 更改单元格的地址低于“标记”范围 => 插入 => 更新变量
• 任何方向都存在差异，即abs(new_row - saved_row) > 0 or abs(new_col-saved_col) > 0=> 固定范围已移动或移位。

Pros:

优点：

• User-defined name is not used
• UsedRangeproperty is not used
• A pinned range is updated accordingly to user actions instead of assumption that a user action will not occur below 1000-th row.

• 不使用用户定义的名称
• UsedRange未使用财产
• 固定范围会根据用户操作进行更新，而不是假设用户操作不会在第 1000 行以下发生。

Cons:

缺点：

• The variable must be assigned in a workbook open event handler in order to use it in a sheet change event handler.
• The variable and a WithEvents-variable of object must be assigned to Nothingin a workbook close event handler in order to unsubscribe form the event.
• It is impossible to determine sort operations due to they change value of range instead of exchange rows.

• 该变量必须在工作簿打开事件处理程序中分配，以便在工作表更改事件处理程序中使用它。
• WithEvents必须Nothing在工作簿关闭事件处理程序中分配对象的变量和变量，以便取消订阅事件。
• 由于它们改变范围的值而不是交换行，因此无法确定排序操作。

The following example shows that both approaches could work. Define in a module:

下面的例子表明这两种方法都可以工作。在模块中定义：

Private m_st As Range
Sub set_m_st()
  Set m_st = [$A:$F]
End Sub
Sub get_m_st()
  MsgBox m_st.Address
End Sub

Then run set_m_st(simply place a cursor in the sub and call Runaction) to pin range $A$10:$F$10. Insert or delete a row or cell above it (don't confuse with changing cell(s) value). Run get_m_stto see a changed address of the pinned range. Delete the pinned range to get "Object required" exception in get_m_st.

然后运行set_m_st（只需将光标放在 sub 和 callRun动作中）以固定 range $A$10:$F$10。插入或删除其上方的行或单元格（不要与更改单元格值混淆）。运行get_m_st以查看固定范围的更改地址。删除固定范围以在get_m_st.