EXCEL VBA 末尾行取得メソッド1 開始行から下へ空白セルを探索 ~シート操作オブジェクト 第３弾~

メソッドの概要

　「EXCEL VBA　シート操作オブジェクト　作成計画」で掲載した「シート操作オブジェクト」の3個目のメソッドとなる「末尾行取得メソッド」の1個目です。後にも書きますが、VBAでは末尾行取得は主に２パターンあるのと、そのほかに考えていることがあるので、「末尾行取得メソッド」の1個目としています。

メソッドを作成する理由

　EXCEL VBAで、データ入力された末尾の行を取得したいことはよくあります。こういう場合、RangeオブジェクトのEndプロパティを使用するのが一般的な方法だと思います。このプロパティは、EXCELで、「CTRL + ↑」や「CTRL + ↓」を押下した時の動作と一緒の機能になります。
　「CTRL + ↓」の動作は、VBAでは「End(xlDown)」を使用し、主にデータに空欄が含まれない場合に、開始行から下へ空欄のセルを探索し、その１個前のセルの行を末尾行とするという使い方になると思います。
　今回は、この「開始行から下へ空欄のセルを探索」についてのメソッドです。

　Endプロパティを使用する方法は私もよく使うのですが、気を付けないとイレギュラーな場合に、データの末尾行を取得できないことがあります。今回の「End(xlDown)」を使用するケースでは、「データが全くない場合」など「開始行が空欄」の場合や、「データが１行目しかない場合」など「開始行はあるが2行目が空欄」の場合がそうです。

例えば、以下のような表の場合

	列	C	D	E	F	G
行
		項目1	項目2	項目3	項目4	項目5
5		AAAAA	AAAAA		AAAAA	AAAAA
6		AAAAA	AAAAA			AAAAA
7		AAAAA	AAAAA			=""
8						AAAAA
9			AAAAA			=""
10			AAAAA
11			AAAAA
12

VBAでEnd(xlDown)を使用すると、以下のようになります。

    Dim sh As Worksheet
    Set sh = ThisWorkbook.Worksheets("Sheet1")
    
    Debug.Print sh.Cells(5, "C").End(xlDown).Row '結果　7
    Debug.Print sh.Cells(5, "D").End(xlDown).Row '結果　7
    Debug.Print sh.Cells(5, "E").End(xlDown).Row '結果　1048576
    Debug.Print sh.Cells(5, "F").End(xlDown).Row '結果　1048576
    Debug.Print sh.Cells(5, "G").End(xlDown).Row '結果　9

　C列では、期待通りの結果です。
　D列のような場合、11行目を末尾行として取得したいのなら、「End(xlUp)」を使うことになるので、今回とは違うケースです。
　そして、E列やF列のような場合が、データの末尾行が取得できていないケースになります。
　
　当然、このようなケースを考慮してコーディングすればいいだけなのですが、　毎回イレギュラーの判定のコードを書くよりは、そのコードをメソッドにまとめておき、メソッドを使用すればデータがないことを判別できたり、1行目にしかデータがなければ1行目が末尾行となるようにしたほうが良いだろうということです。
　
　また、G列は「数式が入力されているが結果が空欄」というセルが含まれていて、この場合、Endプロパティでは「データがある」と判定されます。このケースで、「データを空欄」として末尾を探索したい場合については、後々、掲載しようと思っています。

ソースと使用方法

　本来は、「シート操作オブジェクト」のクラスモジュールである「ttWorkSheetController」に追記するメソッドですが、前回のように、メソッド単体で動かすために、「Class1」モジュールに記載しています。そして、今回のメソッドは、まだ本ソースとなる「ttWorkSheetController」のモジュールには追記しません。
　後々に、上記に書いた、「数式が入力されているが結果が空欄」のセルを「データを空欄」のセルと判定して、末尾行を探索したい場合についての処理を掲載したうえで、本ソースに追記する予定です。
　

ソース

　　Class1

Class1

Option Explicit

Private m_StartRow As Long
Private m_EndRow As Long

'単体として動かすための一時的なプロパティ
    Public m_WorkSheet As Worksheet
    Public WsCtrl As ttWorkSheetController
    
Public Property Get StartRow() As Long
    StartRow = m_StartRow
End Property
Public Property Let StartRow(lng_startrow As Long)
    m_StartRow = lng_startrow
End Property

    
Public Property Get EndRow() As Long
    EndRow = m_EndRow
End Property
Public Property Let EndRow(lng_endrow As Long)
    m_EndRow = lng_endrow
End Property



Public Function getEndRow_DownFromStartRow(Column_Name As String, _
    Optional flg_SpecifyStartRow As Boolean = False, Optional lng_startrow As Long = 0, _
    Optional setProperty As Boolean = True) As Long
'末尾行を取得する。（開始行から、空白の前を探索する）
'　引数
'　　　Column_Name:対象となる列の列名
'　　　flg_SpecifyStartRow:開始行を引数で指定するか
'　　　lng_StartRow:引数で開始行を指定する場合の開始行
'　　　setProperty:末尾行をプロパティに設定するか
    
    Dim end_row As Long
    
    If (m_WorkSheet Is Nothing) Then Err.Raise Number:=10011, Description:="対象シートが設定されていません。"
    
    end_row = private_getEndRow_DownFromStartRow(Column_Name, flg_SpecifyStartRow, lng_startrow)
    
    'setPropertyがTrueなら、末尾行をプロパティにも設定する
    If setProperty = True Then m_EndRow = end_row
    
    getEndRow_DownFromStartRow = end_row
    
End Function


Private Function private_getEndRow_DownFromStartRow(Column_Name As String, _
    flg_SpecifyStartRow As Boolean, Optional lng_startrow As Long _
    ) As Long
    
    Dim start_row As Long
    Dim start_rng As Range
   
    private_getEndRow_DownFromStartRow = -99
    
    '開始行を、プロパティまたは指定の値から取得
    If flg_SpecifyStartRow = True Then
        '「引数で指定する」というフラグflg_SpecifyStartRowが立っていたら
        '引数　lng_StartRow　の値を設定（0やマイナスも設定できるが動きは保証できない）
        start_row = lng_startrow
    Else
        If (m_StartRow <= 0) Or (m_StartRow > m_WorkSheet.Rows.Count) Then
        'プロパティの開始行で処理することを選択し、開始行が不正な場合、このメソッドでは処理を行わない。
            'エラーで処理を中止せず、マイナスの値を返す。
            private_getEndRow_DownFromStartRow = -1
            Exit Function
        Else
            'プロパティの開始行が適正であれば、探索の開始行とする
            start_row = m_StartRow
        End If
    End If
    
    '指定列の開始行にあるセルを、探索開始セルとする
    
'    Set start_rng = m_WorkSheet.Cells(start_row, Me.getColumnNumber(Column_Name))
'   　↑本来はこの構文だがメソッド単体テストのためコメントアウト

    'メソッド単体テストのための構文
        WsCtrl.Sheet = m_WorkSheet
        Set start_rng = m_WorkSheet.Cells(start_row, WsCtrl.getColumnNumber(Column_Name))
    
    '開始行が空欄だった場合
    '  データなし扱い　：　0を返すものとする
    If start_rng.Formula = "" Then
        private_getEndRow_DownFromStartRow = 0
        Exit Function
    End If
    
    '開始行は空欄ではないが、次の行が空欄だった場合
    '　データ１行のみの扱い　　：　　開始行の行番号を返す
    If start_rng.Formula <> "" Then
        If start_rng.Offset(1, 0).Formula = "" Then
            private_getEndRow_DownFromStartRow = start_row
            Exit Function
        End If
    End If
    
    'それ以外の場合は、開始行から下へ探索
    private_getEndRow_DownFromStartRow = start_rng.End(xlDown).Row
    
End Function

使用方法

　データの末尾を知りたい列について、列名とデータの開始行からデータの末尾を探索します。対象列を開始行から下へ探索し、１個目の空白セルの１つ上のセルの行番号がデータの末尾行となります。

　データの開始行は、原則として「シート操作オブジェクト」（今回は、「Class1」）のプロパティに
設定します。引数に、対象となる列の列名を指定してメソッドを呼び出すと、戻り値に末尾行を返し、「シート操作オブジェクト」のプロパティにも末尾行を設定します
　「表の開始行とは別に、ある列についてある行からの末尾行を知りたい」などの場合のために、プロパティとは別に、引数でも開始行を指定して末尾行を取得できます。
　
　また、プロパティの開始行に不正な値が設定されていた場合は、エラーとしてマイナスの戻り値を返しますが、引数で開始行を設定した場合は、開始行が正当であるかの判定をしていません。これは、イレギュラーな使い方を考慮してのためです。
　ただし、この場合、動作について保証しません。処理が止まることもあります。
　
　また、取得された末尾行をプロパティに設定したくない場合、引数で「プロパティに末尾行を設定しない」こともできます。
　

　引数は4つで、必須は1つ、省略可能が3つです。

引数	説明
Column_Name	対象となる列の列名
flg_SpecifyStartRow	開始行を引数で指定するか（省略すると指定しない。プロパティから取得）
lng_StartRow	開始行を引数で指定する場合の開始行（flg_SpecifyStartRowがTrueの場合のみ有効）
setProperty	末尾行をプロパティに設定するか（省略するとプロパティに設定する。）

使用例

Module1

Sub Example1()
    
    Dim obj1 As Class1
    
    Set obj1 = New Class1
    
    Set obj1.m_WorkSheet = ThisWorkbook.Worksheets("Sheet1")
    Set obj1.WsCtrl = New ttWorkSheetController 'メソッド単体のクラスにしたため
    
    'StartRowプロパティに開始行を設定
    obj1.StartRow = 5
    
    Debug.Print obj1.getEndRow_DownFromStartRow("C") '結果 7
    Debug.Print obj1.EndRow '結果 7
    Debug.Print obj1.getEndRow_DownFromStartRow("D") '結果 7
    Debug.Print obj1.getEndRow_DownFromStartRow("E") '結果 0
    Debug.Print obj1.getEndRow_DownFromStartRow("F") '結果 5
    Debug.Print obj1.getEndRow_DownFromStartRow("G") '結果 9
    
    obj1.EndRow = 0
    
    obj1.StartRow = 5
    
    '引数で開始行を設定
    Debug.Print obj1.getEndRow_DownFromStartRow("C", True, 6) '結果　7
    Debug.Print obj1.EndRow '結果　7
    Debug.Print obj1.getEndRow_DownFromStartRow("D", True, 9) '結果　11
    Debug.Print obj1.getEndRow_DownFromStartRow("E", True, 6) '結果　0
    Debug.Print obj1.getEndRow_DownFromStartRow("F", True, 6) '結果　0
    
    obj1.StartRow = 5
    
    'EndRowプロパティに結果を設定しない
    obj1.EndRow = 0
    Debug.Print obj1.getEndRow_DownFromStartRow("C", , , False) '結果　7
    Debug.Print obj1.EndRow '結果　0
    obj1.EndRow = 0
    Debug.Print obj1.getEndRow_DownFromStartRow("D", True, 9, False) '結果　11
    Debug.Print obj1.EndRow '結果　0
    
End Sub

今回はメソッド追加しませんが「ttWorkSheetController」クラスモジュールを掲載します。

　以前に作成したメソッドを今回のメソッド内で使用しているため、今回のソース内で「シート操作オブジェクト」の本ソースとなる「ttWorkSheetController」を使用しています。そのこともありますので、今回はメソッド追加しませんが、「シート操作オブジェクト」の本ソースとなる「ttWorkSheetController」クラスモジュールも掲載します。「VBA　列名取得メソッド　~シート操作オブジェクト　第２弾~」に掲載したものです。
　コードが長いため、折りたたみます。

コードを表示する

ttWorkSheetController

Option Explicit

Private m_WorkSheet As Worksheet

Public Property Get Sheet() As Worksheet
    Set Sheet = m_WorkSheet
End Property

Public Property Let Sheet(sh As Worksheet)
    Set m_WorkSheet = sh
End Property

Public Property Set Sheet(sh As Worksheet)
    Set m_WorkSheet = sh
End Property

Public Function getColumnNumber(ColStr As String) As Integer
'列名などの文字列から列番号を取得
'　　列名だけでなく、列番号（数値型、文字列型を問わない）でも可能。
'　　列ではなくセルでも可能（列番号のみ返す）。
'　　定義されたセルの名前でも可能。
'　　複数列、複数セルの場合は、開始列（左端の列）の列番号を返す。
'　　存在しない列、セルは、エラーとする。
'　　エラーは、負の整数（マイナスの値）を返す
'

    Dim wkColNum As Integer
    Dim wknum As Integer
    Dim errNo As Integer
    
    getColumnNumber = -99
    wknum = -98: errNo = 0
    
    If (m_WorkSheet Is Nothing) Then Err.Raise Number:=10011, Description:="対象シートが設定されていません。"
    
    If IsNumeric(ColStr) = True Then
    '数値の場合
        
        'Columnsプロパティにより列番号の取得を試みる
        On Error Resume Next
        wknum = CInt(ColStr)
        wkColNum = m_WorkSheet.Columns(wknum).Column
        errNo = Err.Number
        On Error GoTo 0
        
        If errNo <> 0 Then
            'エラーとする
            Select Case errNo
            Case 1004
                wkColNum = -1
            Case Else
                wkColNum = -2
            End Select
        End If
            
    Else
    '数値以外の場合
    
        'Columnsプロパティにより列番号の取得を試みる
        On Error Resume Next
        wkColNum = m_WorkSheet.Columns(ColStr).Column
        errNo = Err.Number
        On Error GoTo 0
        
        If errNo <> 0 Then
        'Rangeプロパティで列番号の取得を試みる
            errNo = 0
            On Error Resume Next
            wkColNum = m_WorkSheet.Range(ColStr).Column
            errNo = Err.Number
            On Error GoTo 0
            
            If errNo <> 0 Then
                    wkColNum = -3
            End If
                
        End If
            
    End If
    
    getColumnNumber = wkColNum
    
End Function

Public Function getColumnName(ColNumber As Integer, Optional relative_or_absolute As Integer = 1 _
                                                    , Optional nameOnly_or_rangeAddr As Integer = 1)
'引数に指定された列番号の列の、列名を返す
'  引数  ColNumber:対象列の列番号
'        relative_or_absolute:戻り値が相対参照形式か絶対参照形式か
'                                1:相対参照、2;絶対参照 それ以外は相対参照
'        nameOnly_or_rangeAddr:戻り値が列名単独か、範囲型か
'                                1:列名単独、2:範囲型 それ以外は相対参照

    
    Dim wkColStr As String
    
    getColumnName = ""
    
    If (m_WorkSheet Is Nothing) Then Err.Raise Number:=10011, Description:="対象シートが設定されていません。"
    
    
    wkColStr = m_WorkSheet.Columns(ColNumber).Address
    
    '相対参照（"$"なし）、か絶対参照（"$"つき）か
    If relative_or_absolute = 1 Then
        '相対参照（"$"なし）
        wkColStr = Replace(wkColStr, "$", "")
    ElseIf relative_or_absolute = 2 Then
        '絶対参照（"$"つき）
        wkColStr = wkColStr
    Else
        wkColStr = Replace(wkColStr, "$", "")
    End If
    
    '列名一文字(A)か、列範囲のアドレス(A:A)か
    If nameOnly_or_rangeAddr = 1 Then
        '列名一文字(A)
        wkColStr = Split(wkColStr, ":")(0)
    ElseIf nameOnly_or_rangeAddr = 2 Then
        '列を範囲とした形式のアドレス(A:A)
         wkColStr = wkColStr
    Else
        wkColStr = Split(wkColStr, ":")(0)
    End If
    
    getColumnName = wkColStr
    
End Function