确定用户是添加还是删除行 [英] Determine whether user is adding or deleting rows

问题描述

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

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

有没有区别这两个？

解决方案

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

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

静态lngRow As Long
Dim rng1 As Range
设置rng1 = ThisWorkbook.Names（RowMarker ）.RefersToRange
如果lngRow = 0然后
lngRow = rng1.Row
退出子
结束如果
如果rng1.Row = lngRow然后退出Sub
如果rng1.Row< lngRow然后
MsgBox lngRow - rng1.Row& rows removed
Else
MsgBox rng1.Row - lngRow& rows added
End If
lngRow = rng1.Row
End Sub

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

I am using Worksheet_Change event 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.

Is there anyway to distinguish between those two?

解决方案

You could define a range name such as 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

这篇关于确定用户是添加还是删除行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！