お次は値が入力されている最終列、最大列といいますか、それを取得する関数を公開します。
前回紹介した最終行関数は呼び出し例もなく不親切だったので、呼び出し方法もあわせて紹介いたします。
まずはサンプルコードをご覧ください。
データが入力されている最終列を取得するサンプル
データが入力されている最終列を取得する関数、サンプルコードの使用はすべて自己責任でお願いいたします。
'**************************************************
'名称 :F_GetColMax 最大列位置を取得する
'引数 :robjSheet (I ) Worksheet
' :vlRowNo (I ) Long:KEYとなる行(あれば)
' :rlColMax ( O) Long:(返却用)最大列
' :rsMsg ( O) String:エラーメッセージ
'戻り値:True=成功, False=どこかでエラーになった
'作成 :Kinaccco
'**************************************************
Public Function F_GetColMax(ByRef robjSheet As Worksheet _
, Optional ByVal vlRowNo As Long = 0 _
, Optional ByRef rlColMax As Long = 0 _
, Optional ByRef rsMsg As String = "") As Boolean
On Error GoTo Sub_Err
Dim lbRet As Boolean
Dim lbHit As Boolean
Dim lobjURange As Range
Dim llColMax As Long
Dim llColMaxURange As Long
Dim llRowMaxURange As Long
Dim i As Long, j As Long
'--- 初期値セット ---
lbRet = False
rlColMax = 0
llColMax = 0
rsMsg = ""
'--- パラメータチェック ---
If robjSheet Is Nothing Then
rsMsg = "[ERR-01]Worksheet参照の受け渡しに失敗しました"
GoTo Sub_Exit
End If
'--- UsedRange取得 ---
Set lobjURange = robjSheet.UsedRange
'--- UsedRangeから最終行・最終列を取得する ---
llRowMaxURange = lobjURange.Row + lobjURange.Rows.Count - 1
llColMaxURange = lobjURange.Column + lobjURange.Columns.Count - 1
If vlRowNo = 0 Then
'--- 基準行が省略された場合、UsedRange最終行列から逆順に総なめ ---
lbHit = False
With robjSheet
For i = llColMaxURange To 1 Step -1
For j = llRowMaxURange To 1 Step -1
If Len(Trim(.Cells(j, i).Value)) > 0 Then
lbHit = True
llColMax = i
Exit For
End If
Next j
If lbHit Then Exit For
Next i
If lbHit = False Then
rsMsg = "[ERR-02]最終列の取得に失敗しました"
GoTo Sub_Exit
End If
End With
Else
'--- 基準行がUsedRange最終行より大きい場合エラーとする ---
If vlRowNo > llRowMaxURange Then
rsMsg = "[ERR-03]基準行に指定された値が正しくありません(最終行より大きい)"
GoTo Sub_Exit
End If
With robjSheet
If Len(.Cells(vlRowNo, llColMaxURange).Value) > 0 Then
'--- UsedRange最終列の基準行に値が入力されている場合、UsedRangeの最終列を返す ---
llColMax = llColMaxURange
Else
'--- UsedRange最終列の基準行が空白の場合、シート最終列から終端セルを返す ---
llColMax = .Cells(vlRowNo, .Columns.Count).End(xlToLeft).Column
End If
End With
End If
'--- 返却用変数に格納する ---
rlColMax = llColMax
'--- 正常終了 ---
lbRet = True
Sub_Exit:
On Error Resume Next
Set lobjURange = Nothing
F_GetColMax = lbRet
Exit Function
Sub_Err:
rsMsg = "予期せぬエラーが発生しました" & vbCrLf & _
"プロシージャ名:F_GetColMax" & vbCrLf & _
"エラー番号:" & Err.Number & vbCrLf & _
"エラー内容:" & Err.Description
GoTo Sub_Exit
End Function
呼び出し例
それでは前回紹介した最終行と、今回の最終列取得関数の呼び出し方を説明します
Dim objWB As Workbook
Dim objWS As Worksheet
Dim lngRowMax As Long
Dim lngColMax As Long
'--- Bookを開いてWorksheetの参照を取得する ---
Set objWB = Workbooks.Open("C:Book1.xls")
Set objWS = objWB.Worjsheets("Sheet1")
'--- 最大行を取得する(基準となるKEY列が1列目の場合) ---
If F_GetRowMax(objWS, 1, lngRowMax, strMsg) = False
MsgBox strMsg
Else
MsgBox "最大行:" & Cstr(lngRowMax)
End If
'--- 最大行を取得する(基準となる列がない場合) ---
If F_GetRowMax(objWS, , lngRowMax, strMsg) = False
MsgBox strMsg
Else
MsgBox "最大行:" & Cstr(lngRowMax)
End If
'--- 最大列を取得する(基準となるKEY行が1列目の場合) ---
If F_GetColMax(objWS, 1, lngColMax, strMsg) = False
MsgBox strMsg
Else
MsgBox "最大列:" & Cstr(lngColMax)
End If
'--- 最大列を取得する(基準となる行がない場合) ---
If F_GetColMax(objWS, , lngColMax, strMsg) = False
MsgBox strMsg
Else
MsgBox "最大列:" & Cstr(lngColMax)
End If
'--- 後片付け ---
Set objWS = Nothing
objWB.Close SaveChanges:=False
Set objWB = Nothing
クドい...ですかね、やはり。
たかだか最終行や最終列の取得なのにクドいコードと仰るなかれ。
クドいのには相応の理由があります。
まずこんな表があったとして、いくつかのケースで最終行を取得したいとします。
この表の最終行をUsedRangeを使って取得すると、15が返ります。
でも、UsedRangeなんかで取得できるほどウチの業務は単純じゃねんだよって方、多いと思います。
Excelの表はデータとしてだけでなく、印刷物を配布したりイントラに掲載したりして見るためにデータ以外の文字が入ってる場合もあったり、利用されるシーンはさまざまでしょう。
なんでもIT部門が主役というわけではない。ITワーカーのつらい所です。
データは増えたり減ったりして行数は毎回定まらないけど、合計の数式が入ってる最終行までは要らなくて、商品名が入ってる列の中で最大行が欲しいとき、この例では今のところ14行目となっている行(伝わります?)を動的に取得するために、基準となるKEY列を指定して「F_GetRowMax」を呼び出します。
すべての行で値が入力されているのはこの例では商品名なので、商品名が入っている列=3列目を基準として最終行を求める指定方法は次のように記述します。
If F_GetRowMax(objWS, 3, lngRowMax, strMsg) = False Then
…
F_GetRowMax関数では、最終行を検出したら結果を第3引数に格納して返します。
同様に最終列を取得する場合も、基準となる行が決まっていればその行を指定して「F_GetColMax」を呼びます。
例えば1行目に見出しがあるとしてその最終列を取得する場合は以下のように、基準行に1を指定します。
If F_GetColMax(objWS, 1, lngColMax, strMsg) = False Then
…
F_GetColMax関数では、最終列を検出したら結果を第3引数に格納して返します。
上の表なら、4が返ります。
一方、値が必ず入っている行/列がない場合、どの行もどの列も基準にできないので、次のように基準行/基準列の指定を省略して関数を呼び出します。
If F_GetRowMax(objWS, , lngRowMax, strMsg) = False Then
…
If F_GetColMax(objWS, , lngColMax, strMsg) = False Then
…
この場合返ってくるのはUsedRangeの最終行と最終列です。
正しい値を取得できない?プロパティ・メソッド
多くのサイトで最終行・最終列の取得方法が紹介されていますが、要件を満たさないケースも多いのです。
- .End(xlDown).Row
よく取り上げられている「Sheet.Range("A1").End(xlDown).Row」
これでは、A列で最終行までの間にどこか空白が含まれていたら、正しい最終行が取れません。
調べる列もA列じゃなくて可変にしたい。 - .SpecialCells(xlLastCell).Row
それから「.Cells.SpecialCells(xlLastCell).Row」を使っているパターンもいまだに見かけますが、やめたほうがいい例です。
この「SpecialCells」は保護されたシートでは使えません。
私もかつては使ってましたが、利用部門から指摘されて使うのをやめました。
同様に「CurrentRegion」も保護されたシートでは使用できません。
業務上必要でシート保護することもあるでしょう。自分が知ってるステートメントを使いたいが為にビジネス要件を変えてしまっては本末転倒だと思うのです。
この2つを使用するときはこれらの事に注意する必要があります。
UsedRangeは値だけでなく書式設定も含まれてしまうけど大丈夫
しかし、今回使用したUsedRangeプロパティにも弱点があります。
UsedRangeプロパティの性質なのですが、セルに背景色やら罫線やらコメントやらが設定されているとUsedRangeに含まれてしまうのです。
例えば下図のようなシートでは、UsedRangeの最終セルはC8になってしまいます。
「F_GetRowMax」「F_GetColMax」ではこの対策として、基準列/行が指定されなかった時は、UsedRange最終セルからさかのぼって値が入力されているセルを探して、見つかった行/列を最終と判断するようにしました。
すべてのパターンではないですけど、最終行・列の取得はこの関数でだいたいカバーできそうです。
なんだかプログラミングヲタ心に火が付いちゃって、長い文章になってしまいました。
最終行・最終列の取得は、利用するシーンに合った最適な方法をご自身で探しだしてくださいね!
いつか、お役に立てればうれしいです。