【Excel VBA】For Nextで最大値が自動で設定される方法

はじめに

こんにちは。IMMRです。

以前に「For Nextを使用して、複数のセルを参照することが出来る」と説明しました。

以前に説明した「同じ処理を繰り返し行う」

もし、下記のようなデータがあるとします。

図1　ブック(データの最大値が15)

これに売り上げ数が10個以上なら判定欄に〇を

9個以下なら×をつけるプログラムを作成するとこのようになります。

Dim i As Long

For i = 4 To 15

    If Cells(i, 2) >= 10 Then
        Cells(i, 3) = "〇"
    Else
        Cells(i, 3) = "×"
    End If

Next

では、同じプログラムで下記のデータを使用します。

図2　ブック(データの最大値が14)

セルの範囲は、4～15行です。一応、全てのデータに対して処理をすることが出来ますが

何も値の入っていない14行目と15行目にも処理を行ってしまいます。

つまり、無駄な処理を行ってしまいます。

同じプログラムでまた、異なる下記のデータを使用します。

図3　ブック(データの最大値が16)

今度は、処理のセルの範囲が4～15行に対して、16行目が存在します。

これでは、16行目のデータが存在するにもかかわらず、15行目で処理が終了してしまいます。

データによっては、データ数つまり、行の変化があるものもあります。

そうなると無駄な処理をしてしまったり、全てのデータを処理出来なかったりします。

データ数に応じて、処理の行数を変更しなくてはいけません。

リテラル値にしてしまうと毎回、処理の実行前にFor Next文の最大値(For i=1 To Max)の箇所を変更する必要があります。

処理の度に変更するのは、手間がかかりますし、修正時に他の箇所をいじってしまう恐れもあります。

そのために毎回、処理実行前にデータの行を確認して、その行数をFor NextのMaxに自動で設定されるようにすればよいのです。

その方法を説明します。

1.For Nextで最大値が自動で設定される方法の前に

For Nextで最大値が自動で設定される方法の前にエクセルの[Ctrl] + 矢印キーをご存じですか。

セルの最終行を確認する方法として、

エクセルの右側にあるスクロールバーがあります。

例えば、下記データがあるとします。

図4　ブック(データが記載されている)

そして、右側にあるスクロールバーを下まで移動すると

図5　ブック(スクロールバーでデータが入力されている最大行を確認)

これによって、最終行が4744　であることがわかります。

この方法では、わざわざスクロールをしないといけませんので手間がかかってしまいます。

もっと、手軽に確認する方法があります。

それは、[Ctrl] + 矢印キーです。

行のヘッダー箇所を選択します。

図6　ブック(実行前)

この状態で[Ctrl] + ↓　を押すと

図7　ブック([Ctrl] + ↓の実行結果)

キーボタン一つで最終行のところまで飛ぶことが出来ます。

この方法は同様に、下から上　左から右　右から左も出来ます。

この方法によって最終行のところまで行くことが出来ますが

おすすめとしては、上から下　よりも　下から上です。

なぜかというと

もし、データ内にブランク行が1つ含んでいるとします。

図8　ブック(39行目にブランクが含んでいる)

そして、さきほどのようにヘッダーにカーソルを合わせてから上から下で行うと

図9　ブック(実行前)

図10　ブック([Ctrl] + ↓の実行失敗)

先ほどのブランクのあった39行目の一つ上の行で止まってしまいます。

つまり、本来なら40行目以降にもあるデータがあるにもかかわらず、最終行が38行目となってしまいます。

これでは、全てのデータに対して処理を行うことが出来ません。

しかし、下から上の方法([Ctrl] + ↑)で行えば、そのようなことはなくなります。

先ほどと同じ39行目がブランクのものを使用します。

今度は、ヘッダーでは、なく最終行より下(絶対にこれ以上のデータはない)の行をカーソルに当てます。

ここでは、5000行目にカーソルを置きます。

図11　ブック(実行前)

そして、この状態で、[Ctrl] + ↑　と押すと

図12　ブック([Ctrl] + ↑の実行結果)

データの最終行に飛ぶことが出来ます。

この方法を使えばデータ数が変化してもそれぞれの最終行を調べることが出来ます。

2.For Nextで最大値が自動で設定される方法

For Nextで最大値が自動で設定される方法はマクロで行うと以下のようになります。

上から下

Range("セル場所").End(xlDown).Row

下から上

MsgBox Range("セル場所").End(xlUp).Row

左から右

MsgBox Range("セル場所").End(xlToRight).Column

右から左

MsgBox Range("セル場所").End(xlToLeft).Column

下記、データで行うと

図13　ブック(データが記載されている)

それぞれの最終行は

Range("A3").End(xlDown).Row　⇒　14

Range("A20").End(xlUp).Row　⇒　17

Range("A3").End(xlToRight).Column　⇒　3

Range("G3").End(xlToLeft).Column　⇒　5

となります。

3.For Nextで最大値が自動で設定されるサンプルコード

For Nextで最大値が自動で設定されるかサンプルコードを説明します。

図14　ブック(実行前)

上記のような文字列が記載されているブックを用意して

売り上げ数が10個以上の場合は、判定欄にOKを　9～1個の場合はNGを　ブランクには何も記載しない。

サンプルコード

Sub Test7()

Dim i As Long

    For i = 4 To Range("A30").End(xlUp).Row
        If Cells(i, 2) > 0 Then
            If Cells(i, 2) >= 10 Then
                Cells(i, 3) = "OK"
            Else
                Cells(i, 3) = "NG"
            End If
        End If
    Next i

End Sub

4.For Nextで最大値が自動で設定されるサンプルコードの実行

上記のサンプルコードを使用して、For Nextで最大値が自動で設定されか確認を行います。

実行結果は以下のようになります。

図15　ブック(上記コード実行後)

途中ブランクがあっても最終行まで処理が出来ていることが確認できます。

繰り返し処理(For Next)の最大値を毎回手作業にて入力しないで済みました。