最近VBAを始めました。
C11:H99999の範囲のセルに変更があった時、I列に更新日を表示。C列からH列がすべて空白の行は、I列も消すようにしたつもりなのですが、このマクロだと、C列からH列のどこかを消しただけで、I列も消えてしまいます。
C列からH列がすべて空白の行とのきだけ、I列を消すにはどうしたらいいのでしょうか。アドバイスください。
Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyRng As Range, R As Range
Dim LastUpdated As Integer
Set MyRng = Intersect(Target, Range("C11:H99999"))
If MyRng Is Nothing Then Exit Sub
LastUpdated = 9
For Each R In MyRng.Rows
If WorksheetFunction.CountA(MyRng) = "" Then
Cells(R.Row, LastUpdated).Value = ""
Else
Cells(R.Row, LastUpdated).Value = Date
End If
Next
End Sub
No.3ベストアンサー
- 回答日時:
No.2です。
>元ある値をコピーして複数新しい行を作成した時、複数の行をまとめて削除した時は・・・
そういった操作の場合もあるのですね。
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long
Dim myRng As Range
If Intersect(Target, Range("C:H")) Is Nothing Then Exit Sub
'//▼とりあえず1000行程度限定//
If Target((Target.Count)).Row - Target(1).Row > 1000 Then
MsgBox "範囲が広すぎます"
Exit Sub
End If
'//▼ココから操作//
For i = Target(1).Row To Target(Target.Count).Row
If i > 10 Then
Set myRng = Cells(i, "C").Resize(, 6)
If WorksheetFunction.CountA(myRng) = 0 Then
Cells(i, "I").ClearContents
Else
Cells(i, "I") = Date
End If
End If
Next i
End Sub
今度はどうでしょうか?
※ 列全体などを範囲指定 → Delete のような操作を行うと、「応答なし」の状態になると思います。
今回は1000行程度限定としてみました。m(_ _)m
No.2
- 回答日時:
こんばんは!
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRng As Range
If Intersect(Target, Range("C:H")) Is Nothing Or Target.Count > 1 Then Exit Sub
With Target
If .Row > 10 Then
Set myRng = Range(Cells(.Row, "C"), Cells(.Row, "H"))
If WorksheetFunction.CountA(myRng) = 0 Then
Cells(.Row, "I").ClearContents
Else
Cells(.Row, "I") = Date
End If
End If
End With
End Sub
こんな感じをお望みなのでしょうか?
※ 当方の勘違いなら無視してください。m(_ _)m
No.1
- 回答日時:
こんばんは、
先の質問に投稿しようとしたら締め切られていましたので、先のご質問内容での回答になります。
ほぼ同じと判断しました。
不明な点があれば補足をお願いします。
一部の抜粋かと思いますが、
For Each R In MyRng.Rows
Cells(R.Row, LastUpdated).Value = Date
Next
は、理にかなっていません。なぜなら、Set MyRngのRangeは、Changeなので単一アドレスなのではと思います。
従ってFor Eachループは必要ないと思います。
単純に Cells(Target.Row, LastUpdated).Value = Date で良いかと
>このマクロだと、C列からH列の値を削除したときもI列が変更されてしまいます。
これを回避するなら、Cells(Target.Row, LastUpdated).Value = Dateの実行に条件(制限)をかけてはいかがでしょう。
If Target.Value <> "" Then みたいな感じです。
また、C列からH列の値を削除した時にI列の同じ行を削除したい場合は、If Target.Value <> "" Thenの否定側ElseにCells(Target.Row, LastUpdated).Value = ""を
加えると C11~H、、列のセルを空白にした時にI列の同じ行が空白になります。
本題
>これを、C列からH列すべての行が空白の時はI列も空白にするというマクロを組むにはどこをどのように変更したらいいでしょうか?
すべてがどこに掛かっているか、、理解してないかもですが
If Target.Value <> "" Then の否定側 ElseにCells(Target.Row, LastUpdated).Value = "" のところに行すべてが空白かを調べ実行するようにすればよいですね。
Else
If WorksheetFunction.CountBlank(Range("C" & Target.Row & "H" & Target.Row)) = Range("C" & Target.Row & "H" & Target.Row).Count Then
Cells(R.Row, LastUpdated).Value = ""
End If
CountBlankは数式での空白も含まれます。
Targetを使うのが見にくいなら、ループで使わなくなった変数Rを使って
Set MyRng = Intersect(Target, Range("C11:H99999"))を書き替え
Set R = Intersect(Target, Range("C11:H99999"))
Set R = Intersect(Target, MyRng) でRにしても良いかな。
RにTargetをセットする形なので本当に必要か分かりませんが、、ご質問のコードの流れで、、以降 If R.Value <> "" Then の様に Rで良いかと
この辺は色々、、、
纏めると
Set R = Intersect(Target, Range("C11:H99999"))
If R Is Nothing Then
Exit Sub
Else
Application.EnableEvents = False
If R.Value <> "" Then
Cells(R.Row, LastUpdated).Value = Date
Else
Set MyRng = Range("C" & R.Row & ":H" & R.Row)
If Application.CountBlank(MyRng) = MyRng.Count Then
Cells(R.Row, LastUpdated).Value = ""
End If
End If
End If
Application.EnableEvents = True
あと、セルへの書き込みがあるので、範囲から外れているので良いのですが、一応
Application.EnableEvents を加えました。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) B列に文字がはいったらA列に数字が入るマクロードを完成させたい 4 2023/04/21 01:58
- Visual Basic(VBA) 稀に1円合いません? Sheet1から金額と個数を貼り付ける下記コードで、金額を切り上げるコードを何 3 2022/09/05 15:11
- Visual Basic(VBA) ExcelVBAで、index、match関数を使用して、指定範囲に出力したい 3 2022/10/18 21:53
- Excel(エクセル) 日付で矢印マクロ 4 2023/07/25 16:47
- Visual Basic(VBA) オブジェクトが見つかりません 1 2023/06/24 19:43
- Visual Basic(VBA) excel vbaでvlooupの変数がわかりません。 7 2022/05/30 09:35
- Excel(エクセル) エクセルVBAでオブジェクトが必要です 2 2022/09/10 16:37
- Visual Basic(VBA) エラーコード1004 6 2022/06/09 14:12
- Excel(エクセル) VBAについて 3 2022/06/19 18:19
- Excel(エクセル) VBAの指示の内容 昨日こちらでご教示頂いたのですが初心者な為、一つ一つの指示が何をやっているのかわ 2 2022/10/25 18:08
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
Worksheets メソッドは失敗しま...
-
vba 2つの条件が一致したら...
-
Cellsのかっこの中はどっちが行...
-
【VBA】2つのシートの値を比較...
-
データグリッドビューの一番最...
-
Excelで、あるセルの値に応じて...
-
マクロ 最終列をコピーして最終...
-
DataGridViewに空白がある場合...
-
VBAで、特定の文字より後を削除...
-
rowsとcolsの意味
-
B列の最終行までA列をオート...
-
VBAを使って検索したセルをコピ...
-
VBAで、離れた複数の列に対して...
-
マクロ 関数を使った抽出でエラ...
-
IIF関数の使い方
-
VBAで重複データを確認したい
-
Changeイベントでの複数セルの...
-
VBAのFind関数で結合セルを検索...
-
エクセル アクティブセルから...
-
文字列の結合を空白行まで実行
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Worksheets メソッドは失敗しま...
-
Excelで、あるセルの値に応じて...
-
B列の最終行までA列をオート...
-
vba 2つの条件が一致したら...
-
Cellsのかっこの中はどっちが行...
-
VBAを使って検索したセルをコピ...
-
VBAのFind関数で結合セルを検索...
-
文字列の結合を空白行まで実行
-
IIF関数の使い方
-
【VBA】2つのシートの値を比較...
-
マクロ 最終列をコピーして最終...
-
Changeイベントでの複数セルの...
-
VBA 何かしら文字が入っていたら
-
URLのリンク切れをマクロを使っ...
-
エクセルVBAにて =A1=B1とすれ...
-
VBAでのリスト不一致抽出について
-
データグリッドビューの一番最...
-
マクロについて。S列の途中から...
-
VBA UserFormからの転記で
-
targetをA列のセルに限定するに...
おすすめ情報
ご回答ありがとうございます!
まさしくこのような感じを望んでいました!!
補足といいますか、もう一点わがままなのですが、、、、
元ある値をコピーして複数新しい行を作成した時、複数の行をまとめて削除した時は、I列が変更されません。これを可能にすることはできますでしょうか?
ご回答ありがとうございます!
いろんなサイトのサンプルを見て組み合わせて作成しているのが現状ですので、よく理解していないの部分があります。細かな説明もつけていただきありがとうございます。とても勉強になります。
ご回答いただいたマクロを試したところ動作しませんでした。
値を変更しても更新日は変更されませんでした。
Cells(R.Row, LastUpdated).Value = Dateがエラーになります。
変数宣言などに問題があるのでしょうか。ご指摘願います。
Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyRng As Range, R As Range
Dim LastUpdated As Integer
ご回答いただいたマクロ
End Sub