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

vbalastcelleyecatch

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

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

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

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

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

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

vbarangevscellseyecatchRange VS Cells ！VBAで使えるのはどっち VBAcellareaeyecatchセルの範囲選択と設定は14のツールで対応する vbarowseyecatchRowsプロパティの働きと関連のメソッド vbacolumnseyecatchColumnsプロパティの列指定で参照する vbaentireroweyecatchEntireRowとRowプロパティの違い vbaentirecolumneyecatchEntireColumnとColumnの使い方 vbaoffseteyecatchOffsetプロパティは指定範囲を移動させる vbaresizeeyecatchResizeプロパティでセル範囲をサイズ変更 vbacurrentregioneyecatchCurrentRegionは連続データをまとめて掴む vbaunioneyecatchUnionとRangeの一括選択の働きを比較 SpecialCellseyecatchSpecialCellsメソッドはセル検索し選択する vbausedrangeeyecatchUsedRangeプロパティの使い方のコツ vbaendpropertyeyecatchEndプロパティで上下左右の最終セルを取得 vbalastcelleyecatchデータ入力済セルの最終行番号を取得する

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

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

エクセル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」（値は？）などを付け足すことも出来ます。

End(xlDown)コード 
Sub 行数計算A()

Dim Arow As Long, Aval As Variant

Arow = Worksheets("Sheet1").Cells(4, 1).End(xlDown).Row
Aval = Worksheets("Sheet1").Cells(4, 2).End(xlDown).Value

Debug.Print "Arow " & Arow & vbCrLf & "Aval " & Aval

End Sub

実行結果

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

vbalastcell002

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

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

下から上方向へ数える

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

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

End(xlUp)コード 
Sub 行数計算B()

Dim Arow As Long, Aval As Variant

Arow = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
Aval = Worksheets("Sheet1").Cells(Rows.Count, 2).End(xlUp).Value

Debug.Print "Arow " & Arow & vbCrLf & "Aval " & Aval

End Sub

実行結果

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

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

vbalastcell003

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

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

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

空白行を削除する

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

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

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

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

空白行削除コード 
Sub 行数計算C()

Dim ArowD As Long, ArowU As Long
Dim i As Long, n As Long

ArowD = Worksheets("Sheet1").Cells(4, 1).End(xlDown).Row
ArowU = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row

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」は異なった数値になっていましたが、

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

vbalastcell005

注意事項

  • データ番号はリストの整理番号としての利用になりますので、データ固有のID番号とは別物としてください。

目次へ戻る

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

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

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

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

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

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

これで吉になります。

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

目次へ戻る

エクセルVBAを最初から勉強するために

エラー対処はエクセルVBAでも大きな課題です。

そのためには、エクセルVBAの基本構造も十分知っておくことが必要です。

そんな基本部分から勉強するための記事を書いています。

vbastudyeyecatch002 エクセルVBAを独学で習得する！ために大切な7つのポイントを解説します

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

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

エクセルVBAの独習には動画学習という方法もあります。 目と耳両方を使って学習することでさらに勉強効率を上げることもできると思います。

Udemy1eyecatchエクセルVBA初級者がUdemyで動画学習する講座おすすめ５選と無料講座の上手な使い方。

大村式【動画＆テキスト】Ｅｘｃｅｌマクロ＆ＶＢＡ最高のはじめ方
初めてのVBAの勉強、構えなくても気軽に始められる。
vbastudy020a
vbastudy021a

電子書籍版「大村式【動画＆テキスト】Ｅｘｃｅｌマクロ＆VBA最高のはじめ方」をamazonで見てみる

（著者）大村あつし
（出版社）技術評論社
（税込価格）1,628円（本体1,480円＋税）

学習書の新しい形です。
YouTubeと完全リンクした参考書です。入門と基礎を重点的に２２本の動画で解説をしています。
ちょっとした空き時間を利用してでもスマホがあれば学習ができます。
動画は優しい語り口調で、視聴者にある意味安心感を与えてくれます。動画は5分から20分間ぐらいで22本の構成です。
文章解説と動画解説の関係性は、動画解説が主で、文章解説がサポートいう使い方もできます。
大村式【動画＆テキスト】Ｅｘｃｅｌマクロ＆ＶＢＡ最高のはじめ方 /技術評論社/大村あつし
posted with カエレバ
楽天市場
Amazon
Yahooショッピング
7net

vbabgcoloreyecatchセルの背景色を2クリックで他範囲にも展開できるマクロvbasorteyecatchデータの並び替え VBA新旧のSortを実データで実証vbacellsprotecteyecatch「セルの保護」の設定VBAを最速理解vbacellspaint_gradteyecatch「グラデーションの塗りつぶし」のセルを設定するcellspaintpatterneyecatch「塗りつぶしの網掛け」VBAを最速理解vbacellspaintbackeyecatch「塗りつぶし」背景色をVBAで記述するvbacellsbordereyecatch「罫線」のVBAを最速理解vbacellsfonteyecatch「フォント」の操作を最速理解する エクセルVBAvbacellsplacementeyecatch「配置」を最速理解する エクセルVBAvbacellssyoshikieyecatch「表示形式」をVBAコード的に最速理解するVBAcellareaeyecatchセルの範囲選択と設定は14のツールで対応するvbaendpropertyeyecatchEndプロパティで上下左右の最終セルを取得vbaentirecolumneyecatchEntireColumnとColumnの使い方vbaentireroweyecatchEntireRowとRowプロパティの違いvbacolumnseyecatchColumnsプロパティの列指定で参照するvbaunioneyecatchUnionとRangeの一括選択の働きを比較vbaresizeeyecatchResizeプロパティでセル範囲をサイズ変更vbarangevscellseyecatchRange VS Cells ！VBAで使えるのはどっちvbausedrangeeyecatchUsedRangeプロパティの使い方のコツvbaborder1eyecatchBordersの/位置/線種/太さ/色/と<外枠だけの罫線>の設置vbarowseyecatchRowsプロパティの働きと関連のメソッドvbadeleteeyecatchセルの編集 DeleteとClearの使い分け 削除とクリアの違いvbacurrentregioneyecatchCurrentRegionは連続データをまとめて掴むSpecialCellseyecatchSpecialCellsメソッドはセル検索し選択するvbacharactereyecatchaCharactersで文字列中の一部の文字を加工するvbafontsyseyecatchaFontプロパティで文字装飾操作をするvbajyufukueyecatchエクセルVBA 同じ項目をまとめる（重複データの整理）コード作成vbafindeyecatch001Findメソッドでの検索の方法とコード組み立てのコツvbalastcelleyecatchデータ入力済セルの最終行番号を取得するvbaoffseteyecatchOffsetプロパティは指定範囲を移動させる

このサイトでよく使うVBAのコードのトピックを纏めています。

「エクセルVBA最速理解」の記事一覧を開く

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

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

前の記事

vbawithstateeyecatch
With～End Withの使い方。VBAコードを簡潔に記述す…

次の記事

2つのインプットボックス。関数とメソッド、特徴を生かした使い分…
vbainputboxeyecatch