データーに合わせて罫線を引く

質問

よろしくお願いします。
セル（Ｈ6）を起点にデーターのある部分に罫線を引きたいのですが
計算式（関数）が、入っているセルにも罫線が引かれます。
計算式（関数）は入っているが、データーが入っていない場合は罫線を引かない
方法を教えてください。

現況の記述です。
Sub 罫線()
With Range("H6").CurrentRegion.Borders
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
With .Item(xlInsideHorizontal)
.LineStyle = xlDash
.Weight = xlHairline
.ColorIndex = xlAutomatic
End With
With .Item(xlInsideVertical)
.LineStyle = xlDash
.Weight = xlHairline
.ColorIndex = xlAutomatic
End With
End With
End Sub

回答No.5

DOUGLAS_ · ベストアンサー

「ANo.3」の「お礼」に書かれた シート イベント によって、H7:K105 に計算式が入ったのですが、その計算式の成り立ちをつぶさに見ていくと何となく解ってきました。

　私は、「データ」というのが、いわゆる「定数」のことだと思っていたのですが、そうではなくて、計算式の入っている「H7:K105」の セル 範囲が「何も見えない」状態から「何かデータが入っている（何かが見える）」ような状態になった時に罫線を引くということのようですね。

　この当て推量が当たっているとすれば、
With Range("H6").CurrentRegion.Borders
のところを
With Range("H6").Resize(Application.WorksheetFunction.Count(Range("A7:A105")) + 1, 4).Borders
に代える、あるいは、むしろ、
With Range(Range("H6"), Range("A" & Rows.Count).End(xlUp).Offset(, 10)).Borders
とか
With Range("H6").Resize(Range("A" & Rows.Count).End(xlUp).Row - 5, 4).Borders
に代えると行けそうな気がします。

----------------------------------------

　今回のご質問の ポイント は
With Range("H6").CurrentRegion.Borders
の行にあると思うんですよね。

　「Range("H6")」からどのように範囲を拡大して「罫線を引く」対象にするか、ということが ポイント かと存じますので、もし、上記の変更で行けない場合は、
・「データ」とは何か、「データが入っていない」とは何か
・「データが入っている」という判別は Ｈ列 で行なうのかどうか
などが判るように、下記【問１～４】にお答えください。

　上記の変更で行けた場合は、下記はお見捨てください。

　なお、私は気が長い方ですので、「説明が下手で申し訳ございません。」というようなお気遣いは無用に願います。

========================================

＞数値と文字が混在（Ｈ列は数値、Ｉ列は文字、Ｊ列及びＫ列は数値）です。
　先ず、とても基本的なことを、再度お尋ねいたしますが、「データ」あるいは「数値と文字」とお書きの セル には [ジャンプ] ダイアログ - [セル洗濯(S)...] - [定数(O)] で選択できる「定数」が入っているのですよね？

　見た目は「176」とか「東京都」と書いてあっても、その セル を アクティブ にして、数式バー に現われる文字列の先頭に「=」がある場合は、これは「定数」ではなくて「数式」になります。

　この点を再度、ご確認願います。【問１】

========================================

【問１】の答えが『1211M さんが仰るところの「データ」というのは、[ジャンプ] ダイアログ - [セル洗濯(S)...] - [定数(O)] で選択できるもので、数式バー に現われる文字列の先頭に「=」は入っていない』場合に

＞罫線を引く記述及び計算式の記述のどちらを前に持って行っても、
＞Ｈ6からＫ6までしか、罫線はひくことができません。
　「どちらを前に持って行っても」ではなくて、必ず「罫線を引く記述」が「計算式の記述」の前になければなりません。

　「データ」とお書きのことが、単なる「数字」とか「文字列」ではなくて、計算の結果として、セル に何かが表示されているということになると、話が全く変わってしまいます。

----------------------------------------

＞セルＨ列（Ｈ6：Ｈ105）の中で、データーが入って
＞いる場合は、その行のＨからＫまで罫線を引く。
＞セルＨ列（Ｈ6：Ｈ105）にデーターが入って
＞いない場合は、その行のＨからＫより下は罫線を引かない。

　少し内容が変わったようですね。

　「データ の入っている セル のみ」かと思いきや、「Ｈ列 に データ が入っている場合は、Ｈ列～Ｋ列まで」罫線を引くのですね？【問２】

　そして追加情報として、「Ｈ列 に ブランク がある場合は、それ以下の行は無視する」という理解でよろしいでしょうか？【問３】

　しかも、「データ」の意味によっては、「ブランク」、つまり「データーが入っていない」というのが、本当に何も（計算式も）入っていないのか、あるいは、単に セル に何も表示されていないという意味なのかをお知らせください。【問４】

----------------------------------------

＞但し、計算式はＨ7：Ｋ105まで入っています。（Ｈ6は見出しです）

　ここがよく解らないのですが、上記によると、Ｈ列 にある「データ」・「ブランク」を元にして罫線を引こうとしている訳ですが、Ｈ列 には既に計算式が入っている。
　ということは、この計算式を、手動あるいは マクロ で、<<< 単なる >>>「数字」とか「文字列」として上書きしたり、あるいは、その セル を（[Delete] キーで）クリア して「ブランク」にしている、という理解でよろしいでしょうか？　あるいは、前述のようなことなのか？【問１と問４】

お礼

解りにくい説明にも関わらず、懇切丁寧な回答をしてくださってありがとうございました。

With Range("H6").Resize(Application.WorksheetFunction.Count(Range("A7:A105")) + 1, 4).Borders

この記述でスッキリ解決しました。
今後も、よろしくお願いします。・

回答No.4

DOUGLAS_

＞ちなみに罫線を引く記述と、計算式の記述はシートが
＞アクティブになった時に実行するようにしています。
　ぉゃぁ～ん？

　それって、何だかお約束が違いませんかぁ？

＞計算式（関数）は入っているが、
＞データーが入っていない場合は罫線を
＞<<<<<< 引かない >>>>>
でしたよね。

　お示しの コード が
Private Sub Worksheet_Activate()
の次に書いてあるのでしたら、当該シート が アクティブ になった途端に、J4:K4 及び H7:K105 に計算式が入ってしまいますので、そりゃぁ～

＞Ｈ6からＫ6の行のみ罫線が引かれます
ゎ！

　どういう用途で「データーに合わせて罫線を引く」ということをお考えなのかは存じませんが、
＞罫線を引く記述と、計算式の記述
を１つの シートイベント に同居させた場合、「罫線を引く記述」が先に書いてあるのでしたら、一旦、罫線を引いた後に「計算式の記述」が入りますが、万が一「計算式の記述」の方が先に書いてあるのでしたら、「.SpecialCells(xlCellTypeConstants, 23)」が無くなってしまいますので、
＞Ｈ6からＫ6の行のみ罫線が引かれます
ということになります。

　後は、1211M さんの用途（ニーズ）とよくにらめっこして、どちらを先に（というか、「罫線を引く記述」が後の場合は無意味！）するかということをお考えください。

お礼

ありがとうございます。
罫線を引く記述及び計算式の記述のどちらを前に持って行っても、Ｈ6からＫ6までしか、罫線はひくことができません。

説明が下手で申し訳ございません。

もう一度最初から説明を、し直します

セルＨ列（Ｈ6：Ｈ105）の中で、データーが入っている場合は、その行のＨからＫまで罫線を引く。

セルＨ列（Ｈ6：Ｈ105）にデーターが入っていない場合は、その行のＨからＫより下は罫線を引かない。

但し、計算式はＨ7：Ｋ105まで入っています。（Ｈ6は見出しです）
数値と文字が混在（Ｈ列は数値、Ｉ列は文字、Ｊ列及びＫ列は数値）です。

最終行は変動します。

回答No.3

DOUGLAS_

＞[Ctrl] + [A] を押下したときは、データーのあるなしに
＞関係なく計算式が入っているすべての範囲が入ります。
　この状態で、
＞Ｈ6からＫ6までの行のみ罫線が引かれます。
＞他の行は、データーが表示されているのにも
＞関わらず、罫線なしの状態
となるということは、７行目が ブランク になっているとしか思えませんが。。。

　とりあえず、
＞対象範囲はＨ6を起点にして、データーの最終行は変動
＞但し、計算式はＨ6：Ｋ106まで入っています。
ということでしたら、
With Range("H6").CurrentRegion.Borders
の行を
With Range("H6:K106").SpecialCells(xlCellTypeConstants, 23).Borders
に代えるといかがでしょうか？

　あるいは、「最終行は変動」というのが、１０６行目以下にも及ぶ場合は、
With Range(Range("H6"), Range("H" & Rows.Count).Offset(, 3)).SpecialCells(xlCellTypeConstants, 23).Borders
のようにして、対象範囲を拡大してみてください。

お礼

お手間をおかけします。

７行目もブランクになっていません。
教えていただいた記述を記入したのですが、Ｈ6からＫ6の
行のみ罫線が引かれます。

ちなみに罫線を引く記述と、計算式の記述はシートが
アクティブになった時に実行するようにしています。

計算式は下記の通りですが、何か関係しているのでしょうか。
Dim n As Long, i As Long
n = 0
For i = 7 To 105Range("H" & i).Formula = "=IF(COUNT(A7:A105)>" & n & ",VLOOKUP(" & n + 1 & ",A7:H105,2,FALSE),"""")"
Range("I" & i).Formula = "=IF(COUNT(A7:A105)>" & n & ",VLOOKUP(" & n + 1 & ",A7:H105,3,FALSE),"""")"
Range("J" & i).Formula = "=IF(COUNT(A7:A105)>" & n & ",VLOOKUP(" & n + 1 & ",A7:H105,4,FALSE),"""")"
Range("K" & i).Formula = "=IF(COUNT(A7:A105)>" & n & ",VLOOKUP(" & n + 1 & ",A7:H105,5,FALSE),"""")"
n = n + 1
Next i
Range("J4").Formula = "=SUM(J7:J105)"
Range("K4").Formula = "=SUM(K7:K105)"

回答No.2

DOUGLAS_

＞Ｈ6からＫ6までの行のみ罫線が引かれます。
＞他の行は、データーが表示されているのにも
＞関わらず、罫線なしの状態です。

　「他の行」と書かれるのでしたら、セル番地 ぐらい例示していただいても結構かと存じますね。

　1211M さんご自身が
With Range("H6").CurrentRegion
とお書きですので、
With Range("H6").CurrentRegion.SpecialCells(xlCellTypeConstants, 23)
としましたが、H6 に カーソル を置いて、そこで [Ctrl] + [A] を押下したときに、「他の行」も入っていますでしょうか？

　入っていない、ということであるなら、元々の
With Range("H6").CurrentRegion
という表現が誤りかと存じます。ドコからドコまでを対象範囲にするのか「H2:M10」というような表現でお示しください。

　あるいは、最終行は変動するとか、そのような条件をお示しください。

お礼

ありがとうございます。
下手な説明ですみません。
対象範囲はＨ6を起点にして、データーの最終行は変動します。
但し、計算式はＨ6：Ｋ106まで入っています。
[Ctrl] + [A] を押下したときは、データーのあるなしに関係なく
計算式が入っているすべての範囲が入ります。

回答No.1

DOUGLAS_

＞データーのある部分に罫線を引きたい
＞計算式（関数）は入っているが、データーが入っていない場合は罫線を引かない
ということは、
[ジャンプ] ダイアログ - [セル洗濯(S)...] - [定数(O)]
で選択した範囲でよろしいでしょうか？

　上記でよろしければ、
With Range("H6").CurrentRegion.Borders
の行を
With Range("H6").CurrentRegion.SpecialCells(xlCellTypeConstants, 23).Borders
に変えるだけでいけるかと存じますが、連続（隣接）する セル範囲 が皆無のときには、「xlInsideHorizontal」や「xlInsideVertical」が無効になり エラー が発生します。

　従いまして、連続（隣接）する セル範囲 が１つもない、ということがあり得る場合は、下記のように エラー 処理をなさってください。
　必ず、１つ以上の セル が縦方向に連続（隣接）していて、必ず、１つ以上の セル が横方向に連続（隣接）している場合は、上記の１点を訂正するだけで結構です。

Sub 罫線()
　　With Range("H6").CurrentRegion.SpecialCells(xlCellTypeConstants, 23).Borders
　　.LineStyle = xlContinuous
　　.Weight = xlMedium
　　.ColorIndex = xlAutomatic
　　With .Item(xlInsideHorizontal)
　　　On Error Resume Next
　　　.LineStyle = xlDash
　　　If Err.Number = 0 Then
　　　　.Weight = xlHairline
　　　　.ColorIndex = xlAutomatic
　　　End If
　　　On Error GoTo 0
　　End With
　　With .Item(xlInsideVertical)
　　　On Error Resume Next
　　　.LineStyle = xlDash
　　　If Err.Number = 0 Then
　　　　.Weight = xlHairline
　　　　.ColorIndex = xlAutomatic
　　　End If
　　　On Error GoTo 0
　　End With
　End With
End Sub

お礼

早速の回答ありがとうございます。・
教えていただいた記述を入力したのですが、Ｈ6からＫ6までの行のみ罫線が引かれます。
他の行は、データーが表示されているのにも関わらず、罫線なしの状態です。
どのように治したらよいのでしょうか。
よろしくお願いします。・