(18) ワークシート内にデータ入力した最終行や最終列を VBA で取得する方法！ 

Excel メニューバーの [ヘルプ] - [更新プログラムのチェック] にて、適用されていないアップデートがあれば行ってください。 

Excel 2016 for Mac 環境での説明となりますので、 Mac版 Excel 2011 では（ VBA が利用できますが ）同じ結果にならない場合があるかもしれません。

現状、 VBE 内で日本語を記述すると（貼り付けのみ可能な場合あり）乱れたり、保存時にクラッシュすることが多いため、 VBAコード は「未変換の英数字のみ」で記述してください。（テキストエディタからの貼り付けは可能）

（ ユーザー定義関数 のVBAコードを含んだ）前回使用した XLSM ファイルを、「マクロを有効」にして開きます。

[ alt ] + [ F11 ] にて VBE を開き、Module1 の最後に以下の VBAコードを入力するかコピーして貼り付け。

 1. 【Tips】 [command] + 矢印キーで、最終行や最終列にセル移動可能 

上記画面の赤枠セルのような未入力セルが存在すると、やっかいです！
赤字の "End" は、「 call TestEndProperty1() 」を実行した結果。
緑色の "End" は、その後「 call TestEndProperty2() 」を実行した結果。


「 [command] + 矢印キー 」のキーボードショートカットで、ワークシート内（に入力したデータ）の「最終行」や「最終列」に移動可能です。
（ Windows版だと、[Ctrl] + 矢印キー ）
一度キーボードショートカットを押すと、データ入力済みの「最終行」のセル　あるいは「最終列」のセルに移動し、もう一度押すと、書式（プロパティ）設定済みの「最終行」のセル　あるいは　「最終列」のセルに移動します。

【Tips】のため、覚えておくと便利です。

[ 矢印キー ]
↓ xlDown 選択セルの　最終行に　対応するセルに移動（下方向に検索）
↑ xlUp 選択セルの　最終行に　対応するセルに移動（上方向に検索）
→ xlToRight 選択セルの　最終列に　対応するセルに移動（右方向に検索）
← xlToLeft 選択セルの　最終列に　対応するセルに移動（左方向に検索）

実際にワークシートで操作すると判りますが、下方向　および　右方向に検索した場合に「未入力」のセルが存在するとそのセルに移動してしまい NG です。
つまり、連続してセルにデータが入力されている場合は OK となります。

上記を回避するためには、セルを適切に移動してから　上方向　および　左方向に検索する必要があります。
VBA でもこの操作を行えば、列ごとのデータ入力最終行　または　行ごとのデータ入力最終列　を取得することが可能になります。

 2. VBA でも、最終行や最終列にセル移動が可能 

Sub TestEndProperty1()

    Dim row As Integer
    Dim col As Integer

    ' col A ~ H
    For col = 1 To 8
        With Cells(1, col).End(xlDown).Offset(1, 0)
            .Value = "End"
            .Font.Color = RGB(255, 0, 0) ' Red
        End With
    Next col

    ' row 1 ~ 7
    For row = 1 To 7
        With Cells(row, 1).End(xlToRight).Offset(0, 1)
            .Value = "End"
            .Font.Color = RGB(255, 0, 0) ' Red
        End With
    Next row

End Sub


Sub TestEndProperty2()

    Dim row As Integer
    Dim col As Integer

    ' col A ~ H
    For col = 1 To 8
        With Cells(Rows.Count, col).End(xlUp).Offset(1, 0)
            .Value = "End"
            .Font.Color = RGB(0, 255, 0) ' Green
        End With
    Next col

    ' row 1 ~ 7
    For row = 1 To 7
        With Cells(row, Columns.Count).End(xlToLeft).Offset(0, 1)
            .Value = "End"
            .Font.Color = RGB(0, 255, 0) ' Green
        End With
    Next row

End Sub

上記画面のようにイミディエイトウィンドウから　callステートメント利用（ [return] キー押下 ）で実行が可能です。

TestEndProperty1() が、１行目から「下」方向、あるいは　１列目から「右」方向に検索して、 NG になるパターンです。

TestEndProperty2() が、( Rows.Count ) 行目から「上」方向、あるいは　( Columns.Count ) 列目から「左」方向に検索し、上手く行くパターンです。

「Excel で扱える最大行と最大列」は Excel のバージョンによって異なりますが、最大行は Rows.Count 、最大列は Columns.Count と記述することで常に正しい値がセットされます。

Range (Cells) オブジェクトの End() プロパティ は、実は 最終セルの Range オブジェクトを返します。
プロパティは、値を参照　あるいは　値を設定する場合に用いられる場合がほとんどですが、このように別のオブジェクトを返す場合があります。
よく理解できないかもしれませんが、まれにオブジェクトを返す場合があると覚えてください。

Offset() は以前学習しましたが、 (1, 0) で次行のセルを、 (0, 1) で次列のセルを示します。

列ごとのデータ入力最終行　または　行ごとのデータ入力最終列　を取得できれば、「可変の」くり返し処理を行うことが可能になるため、VBAの応用範囲 がぐっと広がるのです！


次は、 「セル」・「行」・「列」を VBA で扱う方法を復習する予定です。


お疲れ様でした！
[command] + [Q] にてExcel を終了時、「作業中のブック」を保存するか確認のダイアログ画面が表示されるため 「保存」しましょう。
現状、 VBE 内で日本語を記述すると（貼り付けのみ可能な場合あり）乱れたり、保存時にクラッシュすることが多いため、 VBAコード は「未変換の英数字のみ」で記述してください。（テキストエディタからの貼り付けは可能）


最後まで読んでいただき、ありがとうございます。
ぜひ、右側の「人気ブログランキング」投票に　ご協力ください。
また、お越しくださいませ。
// アタル