Worksheet_Changeで変更した行をすべて取得した

Question

下記のようなコードの場合、A3を変更した場合には
"3"を取得することができました。一方でセルの範囲でA2:A5にまとめて
"Hello"をペーストした場合、"2"しか取得できません。
これをすべて取得して順番にFor each等で行ごとに処理することはかのなのでしょうか？

Private Sub Worksheet_Change(ByVal Target As Range)
  If Intersect(Target, Range("A1").EntireColumn) Is Nothing Then
    Exit Sub
    
    Else
    Debug.print Target.Row
  End If
End Sub

Answer 1

ご推察通り、For each で可能です。

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A1").EntireColumn) Is Nothing Then
        Exit Sub
    Else
        Dim r As Range
        For Each r In Target
            Debug.Print r.Row
        Next
    End If
End Sub

Comments

[pegy]

有難うございます！
因みになのですが
A２に何か変化を与えると期待した動作をしてくれるのですが
「行を挿入」をする固まってしまいます。Debug.Printの出力結果は最初の行が無限に
出力されてしまいます。
コードからそのような動作が理解できないのですが、なぜそのような挙動をするのもしわかれば
ご教示いただけますでしょうか？

[hatena19]

なりますね。
プロシージャの先頭に、
Application.EnableEvents = False
を挿入してイベント発生を抑制すればいいかと思いましたが、
それでもだめですね。

[imihito]

「無限に」じゃ無くて16384回な気がしますね

[hatena19]

行挿入なので、Targetは1行全体の範囲になるということですね。
納得です。

Answer 2

すでに解決済みのようですが、、、

下記のようなコードの場合、A3を変更した場合には
"3"を取得することができました。一方でセルの範囲でA2:A5にまとめて
"Hello"をペーストした場合、"2"しか取得できません。
これをすべて取得して順番にFor each等で行ごとに処理することはかのなのでしょうか？

ホントに解決なんですか？

Private Sub Worksheet_Change(ByVal Target As Range)
  If Intersect(Target, Range("A1").EntireColumn) Is Nothing Then
    Exit Sub

    Else
    Debug.Print Target.EntireRow.Address(false,false)
  End If
End Sub

こういう答えを期待してたのでは？

あるいは、こうかな？

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim c As Range
    
    Set Target = Intersect(Target, Me.Columns("A"))
    
    If Not Target Is Nothing Then
        For Each c In Target
            Debug.Print c.Address
        Next
    End If
End Sub

行番号を全部取得する必要があるかどうか判断する材料がないので、
行番号が全部取得できたとして何に使いたいかを、
説明してほしいなと思います。
というか、ほんとにやりたいことを説明してくれないと、
なにか勘違いされてるような気がします。

Answer 3

hatena19さんの回答への補足です。

ExcelのRangeが示すセルの状態は時と場合により異なり、その状態によって各種挙動が変わることがあります。

Worksheet_Changeの引数のTargetの場合は、「セル範囲(単一セル・単一領域範囲・複数領域範囲)」を示し、
For Eachを行うと、Target内のセル1個1個がZ字の順番で取り出されます。

どういうことかというと、行を挿入した場合は新規に挿入された行のセル全てが、Targetに入ります。
そのため、Targetを対象にFor Eachを行うと、その行の1列目のセルから最後の列のセルまでが1個ずつ列挙されます。

対策案はいくつかありますが、汎用的な方法としてはRowsプロパティを使う方法があります。

任意のRangeに対してRowsプロパティを参照すると、参照している範囲自体は変わりませんが、そのRangeを「行毎に区切った範囲」を示すRangeの集合が返されます。
そのため、Target.Rowsに対してFor Eachを行うと、確実に1行ずつのRangeを得られます。
ただし、このRangeは「行毎に区切った範囲」を示しているためやや挙動が特殊になります。
そのため、Cellsプロパティで再度「セル範囲」に戻したほうが扱いやすいです。

他の案として、今回の質問の場合ではA列さえ取得できればいいようにも見えます。
そのため、Intersectで取得した範囲(変更されたセルの内、A列と重なる範囲)を対象にFor Eachしても良いかもしれません。

Private Sub Worksheet_Change(ByVal Target As Range)
'https://teratail.com/questions/
    
    'Intersect(Target, Range("A1").EntireColumn)
    'を無駄に堅牢に書いているだけ
    Dim columnACells As Excel.Range
    Set columnACells = Target.Application.Intersect( _
        Target, _
        Target.Worksheet.Range("A1").EntireColumn, _
        Target.Worksheet.UsedRange _
    )
    If columnACells Is Nothing Then
        Exit Sub
    End If
    
    Dim r As Excel.Range
    For Each r In columnACells
        Debug.Print r.Row, r.Address
    Next r
    
    '参考:汎用的な方法
    If False Then
        For Each r In Target.Rows
            Debug.Print r.Address, 'Targetの各行
            Debug.Print r.Item(1).Address, '上の行と同じ結果(rが行を示す範囲なため)
            Debug.Print r.Cells.Item(1).Address '変更された各行の先頭のセルのアドレス
        Next r
    End If
End Sub

Comments

[hatena19]

A列を選択して、列挿入したり、Deleteキーを押したりすると、1048576回繰り返すことになるので、Intersect(Target, Range("A1:A1000")) というように範囲を制限したほうがいいかもしれませんね。

[imihito]

そこの考慮が漏れていました、ありがとうございます。
今回の用途的には Worksheet.UsedRange と Intersect すれば、おおよそ問題無さそうですかね。