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

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

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

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

広告

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

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 ] の値を返します

例えば1行目に表のラベルがあり、
2行目のセル【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_1
Excel_VBA_End_2
上のコードを実行すると、返ってくる数値が画像になります。
※返ってくる数値が数字だけではなく、文字列も可能性としてある場合、変数は [ Long ] ではなく、 [ Variant ] を使いましょう。

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

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

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

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
Excel_VBA_End_5
上のコードを実行すると、上のような画像の結果が得られます。
返ってくる数値が数字になる場合は変数は [ Long ] で構いません。

.Row】(数、何目?)
.Column】(数、何目?)

これもデータが途切れるまでなので
途中でデータが途切れると、その途切れたところの数値を返します。

Excel_VBA_End_3

 

まとめ

今回の機能はデータ処理においては最も重要なコードの内の1つだ、と
冒頭述べましたが、実際にどんな時に使うかというと、

何行目・何列目までデータ処理(転記・削除・計算…等)しますか?

VBAの魅力と言えば、「自動で処理してくれる」点にあると思います。が、
いったいどこまで処理を続ければ良いのか?ゴールはどこなのか?
コード(指示)が無ければPC自ら判断してくれることはありませんよね。
今回はそのゴール設定を、PC自らがやってくれる(指示する)基本コードなのです。

広告