Excel VBAで可変の行番号を使って範囲(Range)を操作する方法|実例4選

この記事では、Excel VBAにおいて可変の行番号を持つ範囲(Range)を扱う方法を詳しく解説します。具体的には、可変行番号を使った次の4つの操作を学べます。

• セル範囲の選択
• 範囲への数値の入力
• 範囲内での四則演算の実行
• 範囲内のセルへの色付け

いずれも開始セルと行数をユーザーが自由に指定できる汎用性の高いマクロです。業務の自動化にぜひお役立てください。

可変行番号で範囲を設定するVBAコード(全体像)

まず、可変の行番号で範囲を設定する基本となるVBAコードを見てみましょう。

Sub Range_with_Variable_Row_Number()

First_Cell = InputBox("Enter the First Cell of the Range: ")
Row_Number = Str(Range(First_Cell).Row)

Number_of_Rows = InputBox("Enter the Total Number of Rows of the Range: ")

Set Rng = Range(First_Cell & ":" & Mid(First_Cell, 1, Len(First_Cell) - Len(Row_Number) + 1) & Mid(Str(Int(Number_of_Rows) + Int(Row_Number) - 1), 2, 10))

End Sub

⧭ コードの解説:

• このコードは2つの入力を受け取ります。範囲の先頭セル(First_Cell)と、範囲に含める行数(Number_of_Rows)です。
• その後、First_Cellから指定した行数分の範囲をRngとして生成します。
• たとえばFirst_Cellが「B4」、Number_of_Rowsが「10」の場合、出力されるRngは「B4:B13」となります。
• First_Cellの指定には必ず相対参照を使用してください。絶対参照や複合参照($B$4 や $B4 など)は使わないようにしましょう。「B4」のように入力します。

可変行番号でVBA範囲を操作する4つの実例

ここでは、「Jupyter Group」という会社の従業員の氏名と給与をまとめたデータセットを使用します。

このデータセットに対して、VBAで可変の行番号を持つ範囲を操作していきましょう。

1. 可変行番号でセル範囲を選択する

まず最初に、可変の行番号で範囲を選択するマクロを作成します。

ここでは、上位5名の従業員の氏名を選択してみましょう。

以下のVBAコードを使用します。

⧭ VBAコード:

Sub Select_Range()

First_Cell = InputBox("Enter the First Cell to Select: ")
Row_Number = Str(Range(First_Cell).Row)

Number_of_Rows = InputBox("Enter the Number of Rows to Select: ")

Rng = First_Cell & ":" & Mid(First_Cell, 1, Len(First_Cell) - Len(Row_Number) + 1) & Mid(Str(Int(Number_of_Rows) + Int(Row_Number) - 1), 2, 10)

Range(Rng).Select

End Sub

⧭ 実行結果:

マクロ(Select_Range)を実行すると、2つのインプットボックスが表示されます。

1つ目のボックスでは、選択したい範囲の先頭セルを入力します。ここでは最初の従業員のセルである「B4」を入力します。

OKをクリックすると、2つ目のボックスで選択する行数を尋ねられます。ここでは「5」を入力します。

再度OKをクリックすると、データセット内の上位5名の従業員の氏名が選択された状態になります。

2. 可変行番号の範囲に数値を入力する

VBAを使えば、可変の行番号を持つ範囲に数値を入力することもできます。

固定値だけでなく、連続した数値(等差数列)の入力にも対応しています。

ここでは、上位5名の従業員に左側の列(A4:A8)へ「1から5」までの連番を振ってみましょう。

以下のVBAコードを使用します。

⧭ VBAコード:

Sub Insert_Numbers()

First_Cell = InputBox("Enter the First Cell to Insert Number: ")
Row_Number = Str(Range(First_Cell).Row)

Number_of_Rows = InputBox("Enter the Total Number of Rows to Insert Numbers: ")

Set Rng = Range(First_Cell & ":" & Mid(First_Cell, 1, Len(First_Cell) - Len(Row_Number) + 1) & Mid(Str(Int(Number_of_Rows) + Int(Row_Number) - 1), 2, 10))

Series_or_Fixed = Int(InputBox("Enter 1 to Enter a Series of Numbers: " + vbNewLine + vbNewLine + "OR" + vbNewLine + vbNewLine + "Enter 2 to Enter a Fixed Number: "))

If Series_or_Fixed = 1 Then

    First_Number = Int(InputBox("Enter the First Number: "))
    Increment = Int(InputBox("Enter the Increment: "))
    For i = 1 To Rng.Rows.Count
        Rng.Cells(i, 1) = First_Number + (i - 1) * Increment
    Next i

ElseIf Series_or_Fixed = 2 Then
    Number = Int(InputBox("Enter the Fixed Number: "))
    For i = 1 To Rng.Rows.Count
        Rng.Cells(i, 1) = Number
    Next i

End If

End Sub

⧭ 実行結果:

マクロ(Insert_Numbers)を実行すると、複数のインプットボックスが順番に表示されます。

1つ目のボックスでは、数値を入力する先頭セルを指定します。ここでは「A4」を入力します。

OKをクリックすると、2つ目のボックスで数値を入力する合計行数を尋ねられます。ここでは「5」を入力します。

3つ目のボックスでは、連続した数値にするか固定値にするかを選択します。

• 連続した数値の場合:「1」を入力
• 固定値の場合:「2」を入力

ここでは連番を入力したいので「1」を入力します。

4つ目のボックスでは、数列の最初の数値を入力します。「1から5」の数列にしたいので「1」を入力します。

最後のボックスでは増分(公差)を入力します。「1, 2, 3, 4, 5」という数列なら増分は「1」です。

OKをクリックすると、範囲A4:A8に「1から5」までの連番が入力されます。

3. 可変行番号の範囲に対して四則演算を実行する

次に、可変の行番号を持つ範囲に対して数学的な演算を行うマクロを作成します。

たとえば、Jupyter Groupの社長が上位5名の従業員の給与を一律10,000ドル引き上げたいと考えたとしましょう。

この目的を達成するためのマクロを作成します。

以下のVBAコードを使用します。

⧭ VBAコード:

Sub Mathematical_Operation()

First_Cell = InputBox("Enter the First Cell to Perform Operation: ")
Row_Number = Str(Range(First_Cell).Row)

Number_of_Rows = InputBox("Enter the Total Number of Rows to Perform Operation: ")

Set Rng = Range(First_Cell & ":" & Mid(First_Cell, 1, Len(First_Cell) - Len(Row_Number) + 1) & Mid(Str(Int(Number_of_Rows) + Int(Row_Number) - 1), 2, 10))

Operation = Int(InputBox("Enter the Operation to Perform: " + vbNewLine + "Enter 1 for Addition: " + vbNewLine + "Enter 2 for Subtraction: " + vbNewLine + "Enter 3 for Multiplication: " + vbNewLine + "Enter 4 for Division: "))

Operations = Array("Add", "Subtract", "Multiply", "Divide")

Number = Int(InputBox("Enter the Number to " + Operations(Operation - 1) + ": "))

For i = 1 To Rng.Rows.Count

    If Operation = 1 Then
        Rng.Cells(i, 1) = Rng.Cells(i, 1).Value + Number
    End If

    If Operation = 2 Then
        Rng.Cells(i, 1) = Rng.Cells(i, 1).Value - Number
    End If

    If Operation = 3 Then
        Rng.Cells(i, 1) = Rng.Cells(i, 1).Value * Number
    End If

    If Operation = 4 Then
        Rng.Cells(i, 1) = Rng.Cells(i, 1).Value / Number
    End If

Next i

End Sub

⧭ 実行結果:

マクロ(Mathematical_Operation)を実行すると、4つのインプットボックスが表示されます。

1つ目のボックスでは、いつものように演算対象の先頭セルを入力します。

この例では、最初の従業員の給与が入っているセル「C4」を指定します。

2つ目のボックスでは、演算を行う合計行数を入力します。

3つ目のボックスでは、実行したい演算の種類を選択します。

• 足し算:「1」
• 引き算:「2」
• 掛け算:「3」
• 割り算:「4」

ここでは足し算を行いたいので「1」を入力します。

最後に4つ目のボックスで、加算する数値を入力します。ここでは「10000」を入力します。

OKをクリックすると、上位5名の従業員の給与がすべて10,000ドル増加しているのが確認できます。

4. 可変行番号の範囲内のセルに色を付ける

最後に、可変の行番号を持つ範囲のセルに色を付けるマクロを作成します。

ここでは、上位5名の従業員の氏名に色を付けてみましょう。

以下のVBAコードを使用します。

⧭ VBAコード:

Sub Color_Range()

First_Cell = InputBox("Enter the First Cell to Color: ")
Row_Number = Str(Range(First_Cell).Row)

Number_of_Rows = InputBox("Enter the Total Number of Rows to Color: ")

Set Rng = Range(First_Cell & ":" & Mid(First_Cell, 1, Len(First_Cell) - Len(Row_Number) + 1) & Mid(Str(Int(Number_of_Rows) + Int(Row_Number) - 1), 2, 10))

Color_Code = Int(InputBox("Enter the Color Code: " + vbNewLine + "Enter 3 for Color Red." + vbNewLine + "Enter 5 for Color Blue." + vbNewLine + "Enter 6 for Color Yellow." + vbNewLine + "Enter 10 for Color Green."))

Background_or_Text = Int(InputBox("Enter 1 to Color the Whole Background of the Cells: " + vbNewLine + vbNewLine + "Or" + vbNewLine + vbNewLine + "Enter 2 to Color Only the Texts: "))

For i = 1 To Rng.Rows.Count

    If Background_or_Text = 1 Then
        Rng(i, 1).Interior.ColorIndex = Color_Code
    ElseIf Background_or_Text = 2 Then
        Rng.Cells(i, 1).Characters(1, Len(Rng.Cells(i, 1))).Font.ColorIndex = Color_Code
    End If

Next i

End Sub

⧭ 実行結果:

マクロ(Color_Range)を実行すると、4つのインプットボックスが表示されます。

1つ目のボックスでは、色を付けたい先頭セルを入力します。

ここでは最初の従業員のセル「B4」を指定します。

2つ目のボックスでは、色を付ける合計行数を入力します。ここでは「5」です。

3つ目のボックスでは、色を選択します。Microsoftのカラーインデックス(ColorIndex)に従って任意のコードを入力してください。

ここでは黄色を表す「6」を入力しました。

最後のボックスでは、セルの背景全体に色を付けるか、文字だけに色を付けるかを選択します。

ここでは背景全体に色を付けるため「1」を入力します。

OKをクリックすると、上位5名の氏名の背景全体が黄色でハイライト表示されます。

まとめ

この記事で紹介した方法を使えば、Excel VBAで可変の行番号を持つ範囲を柔軟に操作できます。範囲の選択、数値の入力、四則演算、色付けといった日常的なタスクを、開始セルと行数を指定するだけで自動化できるのは大きなメリットです。

ご不明な点があれば、お気軽にお問い合わせください。

関連記事

• VBA Range Offsetの使い方(11の方法)
• Excel VBAで範囲内の各セルを処理する方法(3つの手法)
• Excel VBAで範囲内の各セルをループ処理する完全ガイド
• Excel VBAで範囲を別シートにコピーする最も簡単な8つの方法
• Excel VBA:列内の文字列を検索して行番号を返す方法
• Excelの数式で行番号を増加させる6つの便利な方法