最終列（Column）を取得（空白まで）

2024.02.13

最終列を取得します。空白を検索して検出します。
最終列を取得(最右端から) とは動作が違うので使い分けてください。

C:\ExcelVBA\最終行列取得\sample1.xlsx

例
GetBlankColumn() → 4
GetBlankColumn(ws,1) → 4
GetBlankColumn(ws,2) → 0
GetBlankColumn(ws,3) → 3
GetBlankColumn(ws,5) → 0
GetBlankColumnStr(ws,1) → “D”
GetBlankColumnStr(ws,2) → “”
GetBlankColumnStr(ws,3) → “C”
GetBlankColumnStr(ws,5) → “”

GetBlankColumn(ws,2,2) → 2
GetBlankColumn(ws,6,2) → 3
GetBlankColumnFromRangeStr(ws,”A1″) → ”D”
GetBlankColumnFromRangeStr(ws,”B6″) → “C”

【サンプルプログラム】

Sub sample4()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim filename As String
    Dim last_column As Long

    filename = "C:\ExcelVBA\最終行列取得\sample1.xlsx"

    ' ファイルの確認
    If IsFileExists(filename) = True Then
        ' ブックを開く
        Set wb = Workbooks.Open(filename)
        ' シートを取得
        Set ws = wb.Sheets(1)

        ' 最終列を取得
        last_column = GetBlankColumn(ws)

        ' ブックを閉じる
        wb.Close
        Set ws = Nothing
        Set wb = Nothing

        MsgBox "最終列は" & last_column & vbCrLf, vbInformation
    Else
        MsgBox filename & "は存在しません", vbExclamation
    End If
End Sub

【関数化】

' ------------------------------------------------------------
' 説明：指定した行の空白までの列数を取得（列番号を取得） 1列目(A)の場合は「1」
' 引数：1:処理対象のワークシート 省略時はActiveSheet
'       2:基準となる「行数」を指定 省略時は 1
'       3:基準となる「列数」を指定 省略時は 1
' 戻値：空白までの最終列数
' ------------------------------------------------------------
Function GetBlankColumn(Optional ByVal ws As Worksheet = Nothing, Optional row As Long = "1", Optional col As Long = "1") As Long
    If ws Is Nothing Then
        Set ws = ActiveSheet
    End If
    
    ' タイトル行の列数を取得する
    If ws.Cells(row, col) = "" Then
        ' 1列目が空白の場合は、0列とする (エラーと判断する)
        GetBlankColumn = 0
    ElseIf ws.Cells(row, col + 1) = "" Then
        ' 2列目が空白の場合は、1列とする
        GetBlankColumn = 1
    Else
        ' セルの 1列目 から最終列を取得
        GetBlankColumn = ws.Cells(row, col).End(xlToRight).Column
    End If
End Function
' ------------------------------------------------------------
' 説明：指定した行の空白までの列数を取得（列番号名を取得） 1列目の場合は「A」
' 引数：1:処理対象のワークシート 省略時はActiveSheet
'       2:基準となる「行数」を指定 省略時は 1
'       3:基準となる「列数」を指定 省略時は 1
' 戻値：空白までの最終列数
' 備考：Cellsと同じ、行,列 で指定する (1,1)はA1となる
' ------------------------------------------------------------
Function GetBlankColumnStr(Optional ByVal ws As Worksheet = Nothing, Optional row As Long = "1", Optional col As Long = "1") As String
    Dim result As Long
    
    If ws Is Nothing Then
        Set ws = ActiveSheet
    End If
    
    ' タイトル行の列数を取得する
    If ws.Cells(row, col) = "" Then
        ' 1列目が空白の場合は、0列とする (エラーと判断する)
        GetBlankColumnStr = ""
    ElseIf ws.Cells(row, col + 1) = "" Then
        ' 2列目が空白の場合は、1列とする
        GetBlankColumnStr = "A"
    Else
        ' セルの 1列目 から最終列を取得
        result = ws.Cells(row, col).End(xlToRight).Column
        GetBlankColumnStr = Split(Columns(result).Address, "$")(2)
    End If
End Function
' ------------------------------------------------------------
' 説明：指定した行の空白までの列数を取得（列番号名を取得） 1列目(A)の場合は「1」
' 引数：1:処理対象のワークシート 省略時はActiveSheet
'       2:基準となる Range を指定 省略時は A1
' 戻値：空白までの最終列数
' ------------------------------------------------------------
Function GetBlankColumnFromRange(Optional ByVal ws As Worksheet = Nothing, Optional base As String = "A1") As Long
    If ws Is Nothing Then
        Set ws = ActiveSheet
    End If
    
    ' タイトル行の列数を取得する
    If ws.Range(base) = "" Then
        ' 1列目が空白の場合は、0列とする（エラーと判別する）
        GetBlankColumnFromRange = 0
    ElseIf ws.Range(base).Offset(0, 1) = "" Then
        ' 2列目が空白の場合は、1列とする
        GetBlankColumnFromRange = 1
    Else
        ' セルの 1列目 から最終列を取得
        GetBlankColumnFromRange = ws.Range(base).End(xlToRight).Column
    End If
End Function
' ------------------------------------------------------------
' 説明：指定した行の空白までの列数を取得（列番号名を取得） 1列目の場合は「A」
' 引数：1:処理対象のワークシート 省略時はActiveSheet
'       2:基準となる Range を指定 省略時は A1
' 戻値：空白までの最終列数
' ------------------------------------------------------------
Function GetBlankColumnFromRangeStr(Optional ByVal ws As Worksheet = Nothing, Optional base As String = "A1") As String
    Dim result As Long
    
    If ws Is Nothing Then
        Set ws = ActiveSheet
    End If
    
    ' タイトル行の列数を取得する
    If ws.Range(base) = "" Then
        ' 1列目が空白の場合は、1列とする（エラーと判別する）
        GetBlankColumnFromRangeStr = ""
    ElseIf ws.Range(base).Offset(0, 1) = "" Then
        ' 2列目が空白の場合は、1列とする
        GetBlankColumnFromRangeStr = "A"
    Else
        ' セルの 1列目 から最終列を取得
        result = ws.Range(base).End(xlToRight).Column
        GetBlankColumnFromRangeStr = Split(Columns(result).Address, "$")(2)
    End If
End Function

【修正】
2024/06/22 列番号名で返す関数を追加。Worksheetの引数を省略可能に変更しByValに変更。
2024/08/19 開始位置を行のみから列も追加。Range指定の関数を追加。
Worksheet未指定時にSetが抜けていたので修正。

コメント