【VBA】Excelで罫線を引きたい

VBAでセルの罫線の引き方が分からず、相談させてください。
売上や案件などを月ごとにシートを分けて管理しています。

データ入力する際に空白行が無くなった場合、自動で空白行を挿入するようコードを組みました。
しかし以下のコードが実行された時、空白行は追加されるのですが罫線や書式などがリセットされてしまいます。

.Borders等で罫線が引けることは調べて分かったのですが、その部分のコードの書き方がわかりません。
Target.Rowで空白行の追加された行番号を取得するのか、それとも他に方法があるのか・・・
ご教示いただけますと幸いです。よろしくお願い致します。


【自動で空白行を追加するコード】
'セルにデータ入力時の処理
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

'シート名に"月"が含まれるシートの場合の処理
If InStr(ActiveSheet.Name, "月") >= 1 Then

'セルに"COUNT"関数が含まれている場合の処理
If InStr(ActiveCell.Formula, "COUNT") > 0 Then

'空白行を追加する
Target.Offset(1).EntireRow.Insert Shift:=xlDown

End If

Else
'シート名に"月"が含まれないシートの場合
Exit Sub '何もせず

End If

End Sub

No.3 ベストアンサー 回答者： Qchan1962 回答日時： 2022/07/14 20:02

こんばんは

複数のシートで行いたいと言う事なのでしょうか、
Workbook_SheetChange　を使われたのですね
ActiveCellを使われているようですが、Enterキーの移動設定や範囲選択での入力時に上手くいかない可能性があるのではないでしょうか
Targetを基準にOffsetで数式セルを特定する方が安全かも知れません。。
安全と言いますと・・付け加えますがChangeなので
個別シートのイベント抑制も考慮して
Application.EnableEvents　などを設定しておく必要があると思います。

コードについては＃１様の案が良いと思いますが・・罫線は問題ですね
ちょっと試しに書いて見ますが・・不十分ですかね

新たな実行の条件は入力セルの空白に囲まれた範囲が表の列範囲をカバーしている事です・・上のセル範囲が詰まっていれば良いことになりますが、どうでしょう？
可変でなければ、指定してしまう事で回避できますが複数シートで多分条件が違うかな・・

一例　（色など不十分かな）
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim b As Object, lCol As Long
'シート名に"月"が含まれるシートの場合の処理
If InStr(Sh.Name, "月") >= 1 Then
'セルに"COUNT"関数が含まれている場合の処理
If InStr(Target(1).Offset(1).Formula, "COUNT") > 0 Then
Application.EnableEvents = False
'CurrentRegionのカラム範囲№を取得
lCol = Target(1).CurrentRegion.Item(1).Column
'罫線の取得
Set b = Target(1).Borders(xlEdgeTop)
'行をコピー
Target.EntireRow.Copy
'コピー行を追加する
Target.Offset(1).EntireRow.Insert Shift:=xlDown
'値をクリアー
Target.Offset(1).EntireRow.ClearContents
'罫線の設定（可変列範囲・・列№を指定すれば解り易い
'Target(1).Offset(1, lCol - Target(1).Column)　新規に挿入された行の表下一番左のセル
'Resize(1, Target(1).CurrentRegion.Columns.Count)　CurrentRegionの列数で拡張
With Target(1).Offset(1, lCol - Target(1).Column).Resize(1, Target(1).CurrentRegion.Columns.Count)
.Borders(xlEdgeTop).LineStyle = b.LineStyle
.Borders(xlEdgeTop).Weight = b.Weight
Set b = Nothing
End With
Application.EnableEvents = True
End If
Else
'シート名に"月"が含まれないシートの場合
Exit Sub '何もせず
End If
End Sub

この回答へのお礼

ご回答ありがとうございます。
Application.EnableEventsの設定は仰る通り、設定したほうがいいと感じました。
私の記載したコードですと色々足らなすぎるようで、とんでもなく行がコピーされたりしていたので・・・

Qchan1962様の記載いただきましたコードを基に、肉付けをしていこうと思います！
試しに動かしている状態ですが、何も問題もなくやりたいことができているので、ほとんど完璧だと思います！
ありがとうございました！

お礼日時：2022/07/15 10:44

No.2 回答者： KenKen_SP 回答日時： 2022/07/14 15:40

こんにちは。

テーブルに変換してしまった方がデータ追加に際して書式設定や関数の引き継ぎをコードレスでできると思いますよ。

Youtube で excel テーブル を調べてみて。

ソースにあるように条件がある場合は、本回答はスルーして下さい。

この回答へのお礼

ご回答ありがとうございます。
テーブルは全くの無知ですので、色々調べてみようと思います。

お礼日時：2022/07/15 10:39

No.1 回答者： fujillin 回答日時： 2022/07/14 12:32

こんにちは

ご提示のコードの場合、挿入された行はTargetの1行下になるので
　Target.Offset(1)
の行に書式設定をなされば宜しいと思われます。

一方で、罫線や書式を個々のセルに設定してゆくのは結構面倒ですので、他の行と同じ書式設定であるのなら、「行のコピー挿入」を行ってから値をクリアしてしまう方が簡単ではないかと思います。
（コピーすると、書式等もコピーできますので）

この回答へのお礼

Target.Offset(1)の下へ書式設定を記載したら動きました！
ありがとうございます。

お礼日時：2022/07/14 14:02