データ入力済セルの最終行番号を取得する

vbalastcelleyecatch

エクセルVBAのコード組み立てで、データ入力済のセルの最終行番号を知る必要がある場合は多くあります。

データベースシートを作る場合は必ずと言っていいほどです。

今回はこの知り方について説明をしていきます。

こんにちは、じゅんぱ店長（@junpa33）です。

エクセルVBAのプログラム作成で、入力データをその都度蓄積していく場合など、

既にデータの入っている最終セルの位置を調べたいという時が多くあります。

データ入力済セルの最終行番号を取得するVBAコード

vbalastcellp006kai

どこまでの行と列のセルにデータが入っているかを調べる作業は、

エクセルVBAプログラムを作成する上で、結構な頻度で、必要な組み立てパーツとなることがあります。

「どこまでの行番号にデータが入っているか？」

実際に作表されたエクセルシートなら　”実際に目で見れば”　簡単に判断できます。

それは、人の目で見た場合、データ行が途中で空白行として続いていたとしても　

人が見れば、”ファジーな推測”　で　”たぶんこうだろう”　と判断できるからです。

ところが、VBAコードでその判断を下そうとすると、そうは上手くいきません。

エクセルVBAには、そんな「ファジー・多分このような」を判断する機能なんて無いからです。

シートのセルの使用状況は、その都度異なります。

たとえば、共通の入力フォーマットを作成しても、入力セルの使用状態、その時にデータがどこからどこまで入っているかはその使用者しかわかりません。

「緻密なソフト作成者といえども、使用者が常に同じように狙い通りに使用するとは限らない。」ということになります。

「データ記述の最終行を取得する」という、解説書やサイトの中によく出てくるのは、

このケースには「この方法」を、また、こういったケースには「この方法」を、なんて

対応コードの構文的な「この方法」が何種類も書かれていますが、

その都度、何種類かある中からバラバラと「今回はこれ」なんて使っていられません。また実務者にとっても実用的でもありません。

何故、何がこんなにも　”望む結果を導き出させない”　原因なのかというと、

ズバリそれは、取得対象列に含まれる「空白セル」です。

それがたとえ、「データとしての空白」であったとしてもです。

ですので実務上、

心掛けたいPOINT

データ入力の最終行を調べたい場合は、基本的な前提として出来るだけ、

「使用行を数えるのはどの列か」を「空白セル」が含まれる列を取得の列にしない。

ということが肝心です。

  • データ行の先頭に番号を付与して「空白」の無いデータ列を作るなどの対策をしましょう。
  • どうしても空白行が出来てしまう場合は、データシート上なら、行削除をしてしまう。
    テンプレートシートなら、元々のシート設計でデータシートを作成してそこからのデータ転記になるようにしておく。

ということです。

解説としてはこのような順序で進めて行きます。

行数取得のキーワード　Rows.Count

Rows.Count　とは、「Rows」中学英語で習った複数形　Row（行）の全てということです。すべての行をCount（数えて）くださいということです。

答えは、エクセルバージョン2003なら「65536」、2007以降なら「1048576」になります。

最終行を取得する場合、この　Rows.Count　は重要です。

Endプロパティ

Endプロパティによって、指定したデータ入力のある範囲の終端セルを取得選択します。

上下左右4方向の指定が出来ます。

方向説明記述方法
xlUp下から上方向へEnd(xlUp)
xlDown上から下方向へEnd(xlDown)
xlToleft右から左方向へEnd(xlToleft)
xlToright左から右方向へEnd（xlToright）

説明を進めるための例題を示します

この様な表を考えます。

表の開始行は4行目から、10行目と11行目が空白行となっています。

vbalastcell001

入力行を上から下方向へ数えてどこまでか　xlDown

上から下方向へ数える時に、数え始めのセルを「Cells(4,1)」または「Range(“A4”)」とします。

上から下方向へ数える

構文的には、開始セル「Cells(4,1)」．下方向です「End(xlDown)」となります。

これはセルを選択したと同じ状態ですので、次に何を知りたいかで今回は「.Row」（行番号は？）と付け足します。

ちなみに、「.value」（値は？）などを付け足すことも出来ます。

VBA
Option Explicit

Sub 行数計算A()
    Dim Arow As Long, Aval As Variant
        With Worksheets("Sheet1")
            Arow = .Cells(4, 1).End(xlDown).Row
            Aval = .Cells(4, 2).End(xlDown).Value
        End With
        Debug.Print "Arow " & Arow & vbCrLf & "Aval " & Aval
End Sub

実行結果

見事に「空白」セルで停止してしまいました。（結果を意図するところは20行目のはずですが・・・）

vbalastcell002kai

入力行を下から上方向へ数えてどこまでか　xlUp

数え始めの開始セルは、「Cells(Rows.Count,1)」または「Range(“A” & Rows.Count)」となります。

下から上方向へ数える

構文的には先ほどの下方向へ数える時とほぼ同じです。

「下から上方向に数える」というのが一番使用頻度が多いのではと思います。そういう意味で、先ほどのRows.Countは行番号取得のキーワードになるということです。

VBA
Sub 行数計算B()
    Dim Arow As Long, Aval As Variant
        With Worksheets("Sheet1")
            Arow = .Cells(Rows.Count, 1).End(xlUp).Row
            Aval = .Cells(Rows.Count, 2).End(xlUp).Value
        End With
        Debug.Print "Arow " & Arow & vbCrLf & "Aval " & Aval
End Sub

実行結果

実質的に、臨んだ実行結果になったと思います。

でも、データ表の中に空白行が混ざっているのは気になります。データ番号も飛び番号になってしまっています。

vbalastcell003kai

空白行を削除してデータ番号を整理する方法

この様に空白行やデータ番号の飛び番号を放置していては、バグの原因になりかねません。

そこで、この「End(xlDown)」と「End(xlUp)」を使って空白行を削除しておきます。

空白行を削除する

空白の無い一つのデータ集合体であれば、

チェック

入力行を上から下方向へ数えても、下から上方向へ数えても最終行は同じになります。

もし同じにならないのなら、空白行が存在しているということです。

さらにその場所は、上から下方向に数えた時の最終行の次の行になります。

VBA
Sub 行数計算C()
    Dim ArowD, ArowU As Long
    Dim i, n As Long
        With Worksheets("Sheet1")
            ArowD = .Cells(4, 1).End(xlDown).Row
            ArowU = .Cells(Rows.Count, 1).End(xlUp).Row
        End With
        Debug.Print "ArowD " & ArowD & vbCrLf & "ArowU " & ArowU
        For i = 1 To ArowU
            If ArowD <> ArowU Then
                Rows(ArowD + 1).Delete
            End If
'行を削除したので、全体の行数が変化しました。再度数える必要があります。
            ArowD = Worksheets("Sheet1").Cells(4, 1).End(xlDown).Row
        Next i
'最終行を再確認します。コード組み立て的には必要ありません。
        ArowU = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
        Debug.Print "ArowD " & ArowD & vbCrLf & "ArowU " & ArowU
        For n = 4 To ArowD
            Cells(n, 1).Value = n - 3
        Next n
End Sub

実行結果

このコードの実行で、空白行が削除され、データ番号も連続に振り直しされました。

vbalastcell004

実行前の「ArowDとArowU」は異なった数値になっていましたが、

実行後は同値に変化しました。

vbalastcell005kai
注意事項
  • データ番号はリストの整理番号としての利用になりますので、データ固有のID番号とは別物としてください。
目次へ戻る

データ入力済セルの最終行番号を取得する方法　まとめ

vbalastcellp007kai

最初にも言いましたが、他にも最終行を取得する方法はいくつかあります。

でも実際問題、普通に行うコード組み立てでは、覚えることも使うことも全然必須ではありません。逆に必要ないです。

それよりも、データの「空白行」を作らない。

出来てしまっても「削除修正」を行っておくことに心血を注いだ方がいいです。

ということで、ここでは、「上から下方向へ」と「下から上方向へ」を知っておいてください。

これで吉になります。

また改めて、入力セルの列方向の数え方は記事にしたいと思います。

エクセルVBAを独習するのに参考書は欠かせません。 参考書選びは自分に合った「相棒」にできるものを選んでいきたいです。

vbastudyeyecatch2 エクセルVBAの独習でおすすめ参考書を7冊選ぶ。良書との出会いは大切です

今回の記事はここまでです。   最後までご覧いただき有難うございました。

エクセルVBA最速理解で必要な知識を集めよう！

エクセルVBA業務ツールで日常の業務改善を行いましょう。

＜記事内容についての告知＞

VBAコードの記述記事においては、その記述には細心の注意をしたつもりですが、掲載のVBAコードは動作を保証するものではりません。 あくまでVBAの情報の一例として掲載しています。 掲載のVBAコードのご使用は、自己責任でご判断ください。 万一データ破損等の損害が発生しても当方では責任は負いません。

目次へ戻る

アンケートでポイ活しよう!!

アンケートに答えれば答えるほど ”使える” ポイントがたまります。

NTTコム サーチ

af_banner01

Dstyle web

dstyleweb_logo
dstyle_320x50-min

前の記事

vbaoffseteyecatch
Offsetプロパティは指定範囲を移動させる

次の記事

Findメソッドでの検索の方法とコード組み立てのコツ
vbafindeyecatch001