【VBAコード】データの最下部（最上・下・左・右部）を習得する

2021年11月2日2022年4月30日

Excel_VBA_End_7 — 今回はこの画像の表を例にとって解説していきます。

VBAを使用して、表データ等の処理する際に、今回のテーマは
もっとも重要な事柄の１つになるのではないでしょうか？

実際のPC画面を切り抜いた画像例を用いて説明しますので、
是非ともお仕事等にお役立てください。
　

1. データが何行目・何列目まであるか？を習得する
- 1.1. 【End(xlDown)】は【Ctrl＋[↓]】機能と同じ
- 1.2. 〇行目・〇列目の「〇」の数字を返す方法
2. まとめ

データが何行目・何列目まであるか？を習得する

Option Explicit

Sub Test1()

 Dim mySht1 As Worksheet
 Set mySht1 = ThisWorkbook.Worksheets("Sheet1")
 Dim Endline1 As Variant
 
  Endline1 = mySht1.Range("B1").End(xlDown)
  
  MsgBox Endline1

End Sub

Excel_VBA_End_8 — 例として上のコードを挙げましたが、実行すると、B列の最下部 [ JJJ ] の値を返します

例えば１行目に表のラベルがあり、
２行目のセル【B2】からズラーっとデータが並んでいる場合、
↑のようなコードで最下部を習得するのが良いと思います。
　

【End(xlDown)】は【Ctrl＋[↓]】機能と同じ

Option Explicit

Sub Test2()
     
 Dim mySht1 As Worksheet
 Set mySht1 = ThisWorkbook.Worksheets("Sheet1")
 Dim Endline1, Endline2, Endline3, Endline4 As Variant
 
  Endline1 = mySht1.Range("A1").End(xlDown)      '①
  Endline2 = mySht1.Range("A1").End(xlToRight)   '②
  Endline3 = mySht1.Range("F11").End(xlUp)       '③
  Endline4 = mySht1.Range("F11").End(xlToLeft)   '④
  
  MsgBox Endline1 & vbCrLf & Endline2 & vbCrLf & Endline3 & vbCrLf & Endline4

End Sub

Excel_VBA_End_2 — 上のコードを実行すると、返ってくる数値が画像になります。
※返ってくる数値が数字だけではなく、文字列も可能性としてある場合、変数は [ Long ] ではなく、 [ Variant ] を使いましょう。

このソースコードで使用している【End(xl※※)】は、
キーボード上の【Ctrl＋[矢印キー]】と同じと捉えて下さい。
下にまとめますと、

◆【Ctrl＋[↑]】・・・　End(xlUp)
◆【Ctrl＋[↓]】・・・　End(xlDown)
◆【Ctrl＋[←]】・・・　End(xlLeft)
◆【Ctrl＋[→]】・・・　End(xlRight)
　※データが途切れるまで進む

「データが途切れるまで」、つまり、データが10行迄あって、何かあって６行目だけが空白になっていたら、
得られる(戻り値)は「５行目のデータ」となりますので、注意が必要ですよ。

Option Explicit

Sub Test3()

 Dim mySht1 As Worksheet
 Set mySht1 = ThisWorkbook.Worksheets("Sheet1")
 Dim Endline1 As Variant
 
  Endline1 = mySht1.Range("F1").End(xlDown)
  
  MsgBox Endline1

End Sub

Excel_VBA_End_4 — 空白があると、空白部分でストップするので、上の画像の状態でTest3のVBAを実行すると、このような結果が得られます。

こんな感じです。【Ctrl＋[矢印キー]】を使用した事が無い方は、
是非、VBAで実行する前に、キーボード上で動作を試してみてください。
　

〇行目・〇列目の「〇」の数字を返す方法

そして、この【End(xl※※)】の後ろに【.Row】or【.Column】を加えると
「〇行目のデータ」ではなく「〇」行目の〇の数字を返してくれます。

Option Explicit

Sub Test4()
     
 Dim mySht1 As Worksheet
 Set mySht1 = ThisWorkbook.Worksheets("Sheet1")
 Dim Endline1, Endline2, Endline3, Endline4 As Long
 
  Endline1 = mySht1.Range("A1").End(xlDown).Row       '①
  Endline2 = mySht1.Range("A1").End(xlToRight).Column '②
  Endline3 = mySht1.Range("F11").End(xlUp).Row        '③
  Endline4 = mySht1.Range("F11").End(xlToLeft).Column '④
  
  MsgBox Endline1 & vbCrLf & Endline2 & vbCrLf & Endline3 & vbCrLf & Endline4

End Sub