如何在Worksheet_Change事件期间从目标获取行号

Question

我有以下代码可以检测到单元格值已更改。我想知道单元格属于哪一行。

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range
    ' The variable KeyCells contains the cells that will
    ' cause an alert when they are changed.
    Set KeyCells = Range("F2:F20")
    If Not Application.Intersect(KeyCells, Range(Target.Address)) _
           Is Nothing Then
        MsgBox "Last Contact Date " & Target.Address & " has changed."
        Set Row_number = ???????
    End If
End Sub

Answer 1

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range
    ' The variable KeyCells contains the cells that will
    ' cause an alert when they are changed.
    Set KeyCells = Range("F2:F20")
    If Not Application.Intersect(KeyCells, Range(Target.Address)) _
           Is Nothing Then
        MsgBox "Last Contact Date Row: " & Target.Row & " Column: " & Target.Column & " has changed."
        'Set Row_number = ???????
    End If
End Sub

Answer 2

它可以从 Target 变量中提取，该

变量是一个可以从中提取大量信息的范围，包括 Row。在您的情况下，它看起来像这样：

已更新，以防止用户拥有多行的可能性。关于该怎么做的一些建议。

If Target.Rows.Count > 1 Then
    'do nothing
    'or
    MsgBox "You messed up by selecting " & Target.Rows.Count & " rows!!!", _
    vbCritical, "Come on!"
    'or
    Row_Number = Target.Cells(1, 1).Row 'this will return the top row selection

Else
    'when one row
    Row_Number = Target.Row
End If