検索

キーワード


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

  • 公開日:2020-08-24 15:43:34
  • 最終更新日:2021-03-03 14:15:56

はじめに

こんにちは。IMMRです。

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

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


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

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

For Next文を使ったマクロを実行する前の画面

これに売り上げ数が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] + ↓」で最終行を選択する方法:行のヘッダー箇所の選択

 

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

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

「[Ctrl] + ↓」で最終行を選択する方法:「[Ctrl] + ↓」の実行結果


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

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


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

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


なぜかというと

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

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

途中に空白行があるときの「[Ctrl] + ↓」の挙動:行のヘッダー箇所の選択


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

図9 ブック(実行前)

途中に空白行があるときの「[Ctrl] + ↓」の挙動:「[Ctrl] + ↓」の実行結果


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

途中に空白行があるときの「[Ctrl] + ↓」の挙動:「[Ctrl] + ↓」の実行結果


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

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

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


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


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

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

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

図11 ブック(実行前)

「[Ctrl] + ↑」で最終行を選択する方法:5000行目の選択


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

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

「[Ctrl] + ↑」で最終行を選択する方法:「[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 ブック(データが記載されている)

[Ctrl] + 矢印」を使ったマクロの説明用サンプルデータ


それぞれの最終行は

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 ブック(実行前)

[Ctrl] + 矢印」を使ったマクロを実行する前の画面

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

売り上げ数が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 ブック(上記コード実行後)

[Ctrl] + 矢印」を使ったマクロの実行結果


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

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


【著者】

Imamura

今まではExcel VBAの開発をしてきました。
開発を主にしたものや他の業務の効率化を目指して行ったものさまざまです。
現在は、他のプログラミング言語に挑戦して、スキルアップを目指しています。

よく読まれている記事