,最終行からA1の間に罫線を引く方法は？

質問

お願いします。
罫線を引くマクロを書きました。(下記）
範囲のＪ２６は最終行です。この表のデータ量は変化します。
最終行がＪ２６とは限りません。Ｚ５０００かもしれません。
その範囲に罫線をひくのですが、マクロ的に最終行を認知してA1
まで罫線を引くマクロをどう記述すればよいのか教えてください。
Sheets("読み込み").Select
Range("A1:J26").Select
Range("J26").Activate
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Range("J3").Select
End Sub

回答No.6

KenKen_SP · ベストアンサー

ついでに....

> マクロ的に最終行を認知して

今回の用途では、Find メソッドを利用するのが一番確実でしょう。
ユーザー定義関数や Find メソッド等、初心者にはとっつき難い方法ですが、
最終セルを特定する手法として覚えておいて損はありません。

Sub Sample()

　　Dim rLastCell As Range

　　Set rLastCell = FindLastCell(ActiveSheet)
　　If rLastCell Is Nothing Then
　　　　MsgBox "対象セルはありません", vbInformation
　　Else
　　　　Range(Cells(1, 1), rLastCell).Borders.LineStyle = xlContinuous
　　End If
　　Set rLastCell = Nothing

End Sub

' // シート内の最終セルを求める
Public Function FindLastCell(ByVal Sh As Worksheet) As Range

　　Dim lERow As Long, lECol As Long

　　On Error GoTo Err_
　　lERow = Sh.Cells.Find(What:="*", _
　　　　　　　　　　　　　LookIn:=xlValues, _
　　　　　　　　　　　　　SearchDirection:=xlPrevious, _
　　　　　　　　　　　　　SearchOrder:=xlByRows).Row
　　lECol = Sh.Cells.Find(What:="*", _
　　　　　　　　　　　　　SearchDirection:=xlPrevious, _
　　　　　　　　　　　　　SearchOrder:=xlByColumns).Column
　　Set FindLastCell = Sh.Cells(lERow, lECol)
　　Exit Function

Err_:
　　Set FindLastCell = Nothing
End Function

以下は余談まで。

最終セルを調べる方法として一般に良く知られる方法は、

　・End プロパティーを利用する
　・UsedRange を利用する
　・SpecialCells(xlCellTypeLastCell) を利用する

などがあります。

上記方法のうち、値のある最終行を確実にとれるのは End プロパティーのみです。
ただし、この方法が有効なのは「この列に最終データがある」ということが、予め
判明している場合のみ。そうでなければ、この方法は不確実です。

　　' // A列の最終セルを求める
　　Msgbox Cells(Rows.Count,"A").End(xlUp).Address

その他の UsedRange や SpecialCells の方法では、確かに最終セルがとれるの
ですが、多くの場合期待するセルとは異なります。

例えば、次のテストをしてみて下さい。

1.　A 列全体を選択し、フォントを太字に設定
2.　A1:C5 まで適当なデータを入力

この状況で、次のコードを実行します。

Sub Test1()
　　MsgBox "Usedrange:= " & ActiveSheet.UsedRange.Address
End Sub

結果でわかるとおり、「セルの書式を設定しただけ」で「使用済みセル」となっ
ています。SpecialCells(xlCellTypeLastCell) も同様です。

　# この特性について、これはこれで用途があります。
　# 決して UsedRange や SpecialCells を否定しているわけではありません。

お礼

有難うございます。とても細かく書いていただき感謝です。
貴殿のかいている記述を勉強して理解します。
分からなかったらまた投稿します。
有難うございます。

回答No.5

KenKen_SP

> Sheets("読み込み").UsedRange.Selectを
> 三行の変わりに入れたのですがデバックしました。
> なぜでしょう？

エラーの原因は UsedRange そのものではありません。

1.　Activesheet 以外のものを Select しようとするとエラーになります。
　　つまり、Sheets("読み込み") が非アクティブのとき、このマクロを
　　実行するとエラーになります。
　　直前にそのシートを Activate しましょう。

　　Sheets("読み込み").Activate
　　Sheets("読み込み").UsedRange.Select
　　以下略

2.　シートに使用済みセルがない場合、UsedRange は A1 セルを返します。
　　または、使用済みセルが１つしかない場合も単一セルを返します。
　　この時、

　　> With Selection.Borders(xlInsideVertical)

　　のようにセルの内部罫線を設定しようとするとエラーになります。
　　なぜなら、単一セルには内部罫線をひきようもないからです。

　　Sheets("読み込み").Activate
　　Sheets("読み込み").UsedRange.Select
　　If Selection.Cells.Count=1 Then
　　～
　　End If

　　のように UsedRange の結果、単一セルであったら条件分岐させて
　　内部罫線はひかないようにします。

お礼

有難うございます。
　　Sheets("読み込み").Activate
で解決しました。

回答No.4

n-jun

ANo.3さん
>それと、いきなりシートを指定して、UsedRange.Select をしたので、エラーが発生したのだろうと思います。
>本当は、そのような使い方は考えられないことなのですが。

私も１つ勉強致しました。

回答No.3

Wendy02

こんばんは。

>Sheets("読み込み").UsedRange.Selectを
>三行の変わりに入れたのですがデバックしました。

当然ですが、マクロは、標準モジュールに書いているものとします。シートモジュールではエラーが出ます。

それと、いきなりシートを指定して、UsedRange.Select をしたので、エラーが発生したのだろうと思います。本当は、そのような使い方は考えられないことなのですが。

それ以外のエラーは、シート名が違っているとかでしょう。

>Ｊ２６とは限りません。Ｚ５０００かもしれません。

それと、質問等に出された条件では、「マクロ的に最終行を認知して」ということは出来ません。UsedRangeでも、SpecialCells(xlCellTypeLastCell)　でも、誤作動します。目で見えるものが、必ず、最終行、最終セルではありません。

通常は、End プロパティを使いますが、列も行も決まっていない最終行を取るのは、非常に難しいように思います。

「マクロ的」というのは、VBAのメソッドというように解釈しましたが、最終データを探すメソッドは、厳密にはありません。あくまでも、最終セルは、人為的に操作したセルという意味で、そこにデータがあるとは限りません。

データとしての最終行を１列ずつ、物理的に探さないと不可能なのです。そうでなければ、最終セルを決めるためのルールが必要です。

以下は、あまり良くない見本です。これで試してみれば分かるはずです。最終セルが取れないときがあるはずです。UsedRangeでも同じです。

Sub LineMaking()
Dim LastCell As Range
　　Application.ScreenUpdating = False
　　With Worksheets("読み込み")
　　　　.Select
　　　 Set LastCell = .Cells.SpecialCells(xlCellTypeLastCell)
　　　 .Range("A1", LastCell).Borders.LineStyle = xlNone
　　　　With .Range("A1", LastCell)
　　　　　　　　With .Borders
　　　　　　　　　　.LineStyle = xlContinuous
　　　　　　　　　　.Weight = xlThin
　　　　　　　　　　.ColorIndex = 1 'black
　　　　　　　　End With
　　　　End With
　　　　.Cells(3, LastCell.Column).Select
　　End With
　　 Set LastCell = Nothing
　　Application.ScreenUpdating = True
End Sub

回答No.2

n-jun

>Sheets("読み込み").UsedRange.Select
データがＡ１から始まっているのなら、

Range("A1").CurrentRegion.Select
はどうでしょう？

回答No.1

n-jun

Sheets("読み込み").Select
Range("A1:J26").Select
Range("J26").Activate
の代わりに
Sheets("読み込み").UsedRange.Select

罫線については参考URLを見てみて下さい

参考URL：

http://www.officetanaka.net/excel/vba/cell/cell07.htm

お礼

ありがとうございます。罫線についての細かなサイトで勉強になりました。
Sheets("読み込み").UsedRange.Selectを
三行の変わりに入れたのですがデバックしました。
なぜでしょう？