教えて!ExcelVBA!

ExcelVBAの基礎知識・書き方について説明していきます。

【ExcelVBA セル操作】データが入力されている範囲の最終行/最終列を調べるにはどうすればいいの?教えて!

       f:id:m_kbou:20190522113335p:plain

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

 

【目次】

 

構文

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

 (1)最終行を取得

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

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

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

[説明]:

データが入力されている範囲の最終行を調べるには「Cells(Rows.Count, [列数]).End(xlUp).Row」と記述します。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).End(xlToLeft).Column」と記述します。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」が表示されます。

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文による繰り返し処理
For~Next文による繰り返し処理を行います。変数:列に1~4の値を順次セットして処理を4回実行します。(A列~D列の最終行を順番に確認する必要があるため)

[処理④]:各列の最終行セット
処理が実行される都度、各列の最終行を変数:各列最終行にセットします。

[処理⑤]:IF文による各列最終行の大小比較判定
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」がセットされます。

[処理⑥]:全体最終行の表示
判定した全体最終行の結果が9となったため、MsgBoxには「9」が表示されます。

[実行例]:

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

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」が表示されます。

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文による繰り返し処理
For~Next文による繰り返し処理を行います。変数:列に1~4の値を順次セットして処理を4回実行します。(1行目~4行目の最終列を順番に確認する必要があるため)

[処理④]:各行の最終列セット
処理が実行される都度、各行の最終列を変数:各行最終列にセットします。

[処理⑤]:IF文による各列最終行の大小比較判定
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」がセットされます。

[処理⑥]:全体最終列の表示
判定した全体最終列の結果が4となったため、MsgBoxには「4」が表示されます。

[実行例]:

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

f:id:m_kbou:20190522133329p:plain

最終列を調べる方法についての説明は以上です。

 

おわりに

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