まさ工房

ExcelVBAとAcessDBを利用した業務支援ツールを作成する工房です

【Excelマクロ・VBA】データが入力されている範囲の最終行/最終列を取得するにはどうすればいいの?教えて!

       f:id:m_kbou:20190522113335p:plain

今回はデータが入力されている範囲の最終行や最終列の取得方法について説明します。

 

【目次】

 

構文

最終行及び最終列を取得する方法は、以下の通りとなります。

 (1)最終行を取得

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

Cells(Rows.Count, [列数]).End(xlUp).Row

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

[説明]:

「Cells(Rows.Count, [列数])」の部分は、検索を開始するセルを記述します。Rows.Countは、使用しているExcelの最終行(例:Excel 2007以降の形式ならば「1048576」、それ以前であれば「65536」が戻り値)との意味です。また、[列数]には、最終行を調べたい対象の列数(A列を調べたい場合は「1」)を記述します。End(xlUp)」は開始セルから上方向に向かって検索するとの意味になります。また、「Row」は行に対してとの意味になります。内容を纏めると、「検索を開始するセルから上方向に向かって検索し、最初にデータが入力されている行数を教えて下さい。」との意味になります。

[記述例]:

Cells(Rows.Count, 1).End(xlUp).Row

 

(2)最終列を取得

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

Cells([行数], Columns.Count).End(xlToLeft).Column

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

[説明]:

「Cells([行数], Columns.Count)」の部分は、検索を開始するセルを記述します。Columns.Countは、使用しているExcelの最終列(例:Excel 2007以降の形式ならば「16384」、それ以前であれば「256」が戻り値)との意味です。また、[行数]には、最終列を調べたい対象の行数(1行目を調べたい場合は「1」)を記述します。End(xlToLeft)」は開始セルから左方向に向かって検索するとの意味になります。また、「Column」は列に対してとの意味になります。内容を纏めると、「検索を開始するセルから左方向に向かって検索し、最初にデータが入力されている列数を教えて下さい。」との意味になります。

[記述例]:

Cells(1, Columns.Count).End(xlToLeft).Column

 

使い方

使い方について、具体的に説明していきます。

最終行を取得する方法

最終行を取得する方法について説明します。

【例題1】:入力されているデータ範囲が1列しかない場合

入力されているデータ範囲が1列しかない場合について、最終行の取得方法について説明します。

f:id:m_kbou:20190522113402p:plain

[記述例]:

※以下の記述例は、VBE(VBA記述画面)に記述しないと実行ができません。VBEの開き方については、VBEの開き方を参考にして下さい。

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

Sub サンプル()
    MsgBox Cells(Rows.Count, 1).End(xlUp).Row
End Sub

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

内容は、「A1048576セルから上方向に向かって検索し、最初にデータが入力されている行数をMsgBoxで表示して下さい。」との意味になります。

[実行例]:

結果は、データが入力されている最終行である「7」がMsgBoxに表示されます。

f:id:m_kbou:20190522113424p:plain

【例題2】:入力されているデータ範囲が複数列ある場合

入力されているデータ範囲が複数列あり、各列に入力されているデータの最終行がバラバラな場合、最終行の取得方法について説明します。今回はFor~Next文を使用して列数分の処理を繰り返し、表中データの最終行を取得します。

f:id:m_kbou:20190522113444p:plain

[記述例]:

※以下の記述例は、VBE(VBA記述画面)に記述しないと実行ができません。VBEの開き方については、VBEの開き方を参考にして下さい。

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

Sub サンプル()
    '↓処理①
    Dim 列 As Double
    Dim 各列最終行 As Double
    Dim 全体最終行 As Double
    '↓処理②
    各列最終行 = 0
    全体最終行 = 0
    '↓処理③
    For 列 = 1 To 4
        '↓処理④
        各列最終行 = Cells(Rows.Count, 列).End(xlUp).Row
        '↓処理⑤
        If (全体最終行 < 各列最終行) Then
            全体最終行 = 各列最終行
        End If
    Next
    '↓処理⑥
    MsgBox 全体最終行
End Sub

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

処理の流れは以下の通りとなります。

[処理①]:変数の定義をします
Dimによる変数の定義となります。変数名は「列」と「各列最終行」と「全体最終行」となり、入力できる値はいずれも「小数を含む数値のみ」となります。
[処理②]:変数に初期値をセットします
[処理①]で定義した変数:各列最終行と全体最終行に初期値0をセットします。
[処理③]:For~Next文による処理回数の記述となります
変数:列に1~4の値を順次セットして処理を4回実行します。(A列~D列の最終行を順番に確認する必要があるため)[処理④]では、処理が回る都度各列の最終行を変数:各列最終行にセットします。
[処理⑤]:IF文による各列最終行の大小比較判定記述となります
ここでは変数:全体最終行にA列~D列の各列最終行を大小比較しながらセットします。1回目の処理では、[処理②]で変数:全体最終行にセットした初期値(0)と[処理④]でセットしたA列最終行(7)を比較します。結果、0<7の条件が一致するため、変数:全体最終行には「7」がセットされます。2回目の処理では、変数:全体最終行(7)と[処理④]でセットしたB列最終行(5)を比較しします。結果、7<5の条件が不一致となるため、変数:全体最終行(7)はそのままととなります。3回目の処理では、変数:全体最終行(7)と[処理④]でセットしたC列最終行(1)を比較します。結果、7<1の条件が不一致となるため、変数:全体最終行(7)はそのままととなります。4回目の処理では、変数:全体最終行(7)と[処理④]でセットしたD列最終行(9)を比較します。結果、7<9の条件が一致するため、変数:全体最終行には「9」がセットされます。
[処理⑥]:MsgBoxによる全体最終行の表示をします
[処理⑤]で判定した全体最終列の結果が9となったため、MsgBoxには「9」が表示されます。

[実行例]:

結果は、D列の最終行「9」がMsgBoxに表示されます。

f:id:m_kbou:20190522113500p:plain

データ範囲が複数列ある場合の最終行取得方法についての説明は以上です。

 

最終列を取得する方法

最終列を取得する方法について説明します。 

【例題1】:入力されているデータ範囲が1行しかない場合

入力されているデータ範囲が1行しかない場合について、最終列の取得方法について説明します。

f:id:m_kbou:20190522133308p:plain

[記述例]:

※以下の記述例は、VBE(VBA記述画面)に記述しないと実行ができません。VBEの開き方については、VBEの開き方を参考にして下さい。

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

Sub サンプル()
    MsgBox Cells(1, Columns.Count).End(xlToLeft).Column
End Sub

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

内容は、「XFD1セルから左方向に向かって検索し、最初にデータが入力されている列数をMsgBoxで表示して下さい。」との意味になります。

[実行例]:

結果は、データが入力されている最終列である「4」がMsgBoxに表示されます。

f:id:m_kbou:20190522133329p:plain 

【例題2】:入力されているデータ範囲が複数行ある場合

入力されているデータ範囲が複数行あり、各行に入力されているデータの最終列がバラバラな場合、最終列の取得方法について説明します。今回もFor~Next文を使用して行数分の処理を繰り返し、表中データの最終列を取得します。

f:id:m_kbou:20190522133401p:plain

[記述例]:

※以下の記述例は、VBE(VBA記述画面)に記述しないと実行ができません。VBEの開き方については、VBEの開き方を参考にして下さい。

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

Sub サンプル()
    '↓処理①
    Dim 行 As Double
    Dim 各行最終列 As Double
    Dim 全体最終列 As Double
    '↓処理②
    各行最終列 = 0
    全体最終列 = 0
    '↓処理③
    For 行 = 1 To 4
        '↓処理④
        各行最終列 = Cells(行, Columns.Count).End(xlToLeft).Column
        '↓処理⑤
        If (全体最終列 < 各行最終列) Then
            全体最終列 = 各行最終列
        End If
    Next
    '↓処理⑥
    MsgBox 全体最終列
End Sub

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

処理の流れは以下の通りとなります。

[処理①]:変数の定義をします
Dimによる変数の定義となります。変数名は「行」と「各行最終列」と「全体最終列」となり、入力できる値はいずれも「小数を含む数値のみ」となります。
[処理②]:変数に初期値をセットします
[処理①]で定義した変数:各行最終列と全体最終列に初期値0をセットします。
[処理③]:For~Next文による処理回数の記述となります
変数:行に1~4の値を順次セットして処理を4回実行します。(1行目~4行目の最終列を順番に確認する必要があるため)[処理④]では、処理が回る都度各行の最終列を変数:各行最終列にセットします。
[処理⑤]:IF文による各行最終列の大小比較判定記述となります
ここでは変数:全体最終列に1行目~4行目の各行最終列を大小比較しながらセットします。1回目の処理では、[処理②]で変数:全体最終列にセットした初期値(0)と[処理④]でセットした1行目最終行列(3)を比較します。結果、0<3の条件が一致するため、変数:全体最終列には「3」がセットされます。2回目の処理では、変数:全体最終列(3)と[処理④]でセットした2行目最終列(2)を比較しします。結果、3<2の条件が不一致となるため、変数:全体最終列(3)はそのままととなります。3回目の処理では、変数:全体最終列(3)と[処理④]でセットした3行目最終列(1)を比較します。結果、3<1の条件が不一致となるため、変数:全体最終列(3)はそのままととなります。4回目の処理では、変数:全体最終列(3)と[処理④]でセットした4行目最終行(4)を比較します。結果、3<4の条件が一致するため、変数:全体最終列には「4」がセットされます。
[処理⑥]:MsgBoxによる全体最終列の表示をします
[処理⑤]で判定した全体最終列の結果が4となったため、MsgBoxには「4」が表示されます。

[実行例]:

結果は、4行目の最終列「4」がMsgBoxに表示されます。

f:id:m_kbou:20190522133329p:plain

データ範囲が複数行ある場合の最終列取得方法についての説明は以上です。

 

おわりに

データ範囲の最終行や最終列の値が取得できると、以降の処理でその値を活用したデータの追加・変更・削除等のプログラミングが容易になります。是非有効に活用して下さい。