Office TANAKA - Excel VBA Tips[連続データを作成する]

Excelには、さまざまな便利機能が搭載されていますが、その中でも「連続データの作成」は、かなり画期的な機能だと思います。やり方は簡単です。任意のセルに、連続データのひとつを入力し、入力したセルのフィルハンドルをドラッグするだけです。

90年代に開発された、かなり初期のバージョンから実装されている古参機能で、正式には「オートフィル」といいます。ちなみに、オートフィル機能には、あまり知られていない多くの仕組みが搭載されているのですが、それはまた別のコンテンツでご紹介します。今回のテーマは、このオートフィルをVBAで実現する方法です。これ、知っていると思わぬところで役立ちます。

使うのはAutoFillメソッドです。

セル.AutoFill Destination, Type

「セル」は、フィルハンドルをドラッグするセルのことです。一般的には「連続データのひとつが入力されている最初の1セル」を指定することが多いですが、ここに複数のセルを指定することも可能です。AutoFillメソッドには2つの引数が用意されています。ひとつめの引数Destinationには、「連続データを埋め込むセル範囲」を指定します。ここには、「セル」に指定した「連続データのひとつが入力されている最初の1セル」を含める点に留意してください。上図の動作で言えば、「セル」がRange("A1")で、DestinationにはRange("A1:A5")を指定します。ふたつめの引数Typeは省略可能です。ここには、どのような処理をするかを表す、次の定数を指定します。

定数	値	説明
xlFillDefault	0	何を埋め込むかExcelが判断します(既定値)
xlFillCopy	1	セルをコピーします
xlFillSeries	2	等間隔の数値を埋め込みます
xlFillFormats	3	書式のみを適用します
xlFillValues	4	書式を適用せず、値のみを埋め込みます
xlFillDays	5	"日"を連続データの対象にします
xlFillWeekdays	6	"週日"を連続データの対象にします
xlFillMonths	7	"月"を連続データの対象にします
xlFillYears	8	"年"を連続データの対象にします
xlLinearTrend	9	加算した数値を埋め込みます
xlGrowthTrend	10	乗算した数値を埋め込みます

上の表を見て「はぁ？」って感じた方もいるのでは？先に言ったように、実は「オートフィル」って、あまり知られていない多くの仕組みが用意されているんです。このへんの話をすると長くなりますし、そもそもオートフィルって何？という解説になってしまいますので、本コンテンツでは割愛します。そのうち別の"機能編"で詳解します。いずれにしても、引数Typeには、このオートフィルをどう使うのかを指定します。ほとんどの場合は、xlFillSeries(等間隔の数値を埋め込みます)またはxlFillDefault(何を埋め込むかExcelが判断します)でしょう。

では、簡単なケースでやってみましょう。

Sub Macro1()
    Range("A1") = "A-101"
    Range("A1").AutoFill Range("A1:A5")
End Sub

Range("A1") → 「連続データのひとつが入力されている最初の1セル」
Range("A1:A5") → 「連続データを埋め込むセル範囲」

です。繰り返しますが、「連続データを埋め込むセル範囲」の中には「連続データのひとつが入力されている最初の1セル」を含めるのを忘れないようにしてください。なお、上記Macro1では、引数Typeを省略して、どんな値を埋め込むのかExcelに判断させています。

次は日付でやってみましょう。

Sub Macro2()
    Range("A1") = "2021/4/1"
    Range("A1").AutoFill Range("A1:A5")
End Sub

まぁ、これもイメージしやすいですね。では、ここまで省略してきた引数Typeを指定するケースをお見せしましょう。たとえば、こんな感じです。

Sub Macro3()
    Range("A1") = "2021/4/1"
    Range("A1").AutoFill Range("A1:A5"), xlFillMonths
    ''"月"を連続データの対象にします
End Sub

Sub Macro4()
    Range("A1") = "2021/4/1"
    Range("A1").AutoFill Range("A1:A5"), xlFillYears
    ''"年"を連続データの対象にします
End Sub

オートフィルでは、こんなこともできるんですよ。もちろん手動操作でも可能です。さて、引数Typeを省略するとxlFillDefault(何を埋め込むかExcelが判断します)とみなされるのですが、「連続データのひとつ」として最初のセルに指定する値が、純粋な数値だった場合、Excelは連続データではなく、セルのコピーを実行してしまいます。

Sub Macro5()
    Range("A1") = "101"
    Range("A1").AutoFill Range("A1:A5")
End Sub

これはExcelの仕様ですから、しかたありません。こんなときは、引数TypeにxlFillSeries(等間隔の数値を埋め込みます)を指定します。

Sub Macro6()
    Range("A1") = "101"
    Range("A1").AutoFill Range("A1:A5"), xlFillSeries
End Sub

VBAからオートフィルを活用するためには、必須の技術が2つあります。ひとつめは「引数Destinationの指定方法」です。ここまでは、オートフィルで作成する連続データの範囲を、Range("A1:A5")と固定していました。実務では、ここが可変になることが多いです。では例として「5個の連続データを作る」ケースで考えてみましょう。与えられる情報は

連続データの1つめ
連続データを作成する個数

です。ここでは

連続データの1つめ → 「A-101」
連続データを作成する個数 → 「5」

とします。

Sub Macro7()
    Range("A1") = "A-101"
    Range("A1").AutoFill Range("A1")から下に5個分のセル範囲
End Sub

「Range("A1")から下に5個分のセル範囲」は「Range("A1:A5")」です。これを与えられた「5」という数値を使って表すと「Range("A1").Resize(5)」となります。くれぐれも「Range("A1:A" & 5)」なんて下品で無粋な発想はしないでくださいね。

Sub Macro7()
    Range("A1") = "A-101"
    Range("A1").AutoFill Range("A1").Resize(5)
End Sub

このように、あえて3カ所を「Range("A1")」で統一すると、この「Range("A1")」をWithで括ることができます。

Sub Macro7()
    With Range("A1")
        .Value = "A-101"
        .AutoFill .Resize(5)
    End With
End Sub

Resizeがよく分からないという方は、下記の動画をご覧ください。実務では必須の、OffsetとResizeについて、よくある間違いも含めて詳しく解説しています。

さて、VBAでオートフィルを活用するために欠かせない、ふたつめの技術は「どこに連続データを作成するのか」です。ここまでは解説のために、アクティブシートのA列に連続データを作ってきました。しかし、実務で使うワークシートでは、いつもA列が空いているなんてことはありません。では、どこに連続データを作成するのかというと、これには2通りの考え方があります。

作業中のワークシートで、選択中のセル範囲に作成する
非表示のワークシートを作って、そこに作成する

です。

まずは簡単な、1.からいきましょう。次のような状態だったとします。

この選択されているセル範囲が、どこかを知る必要はありません。次のように考えます。まず、選択されているセル範囲は「Selection」という単語で表されます。連続データを作成するために必要な情報は

連続データの1つめ
連続データを作成する個数

の2つでした。このうち「連続データの1つめ」は、選択されているセル範囲(Selection)の先頭セル(1つめのセル)ですから、「Selection(1)」で表されます。さて、連続データを作成するには、あともうひとつ「連続データを作成する個数」が必要です。でも待ってください。なぜ個数が必要だったのでしょう。それは、AutoFillで連続データを作成するセル範囲を特定するためでした。今回のように、あらかじめ「連続データを作成するセル範囲が選択されている」のでしたら、「連続データを作成するセル範囲＝選択されているセル範囲」です。なので、作成する個数を調べるまでもなく、引数Destinationには「Selection」を指定すればいいです。とはいえ、さらに高度な使い方をするためにも、個数を調べる方法も知っておいてください。今回のケースでは「5」です。この「5」は、選択されているセルの個数ですよね。したがって「Selection.Count」で分かります。

したがって、選択されているセル範囲内に直接連続データを作成するのでしたら、次のようになります。

Sub Macro8()
    Selection(1).AutoFill Selection
End Sub

さて、今回お送りしている「VBAでオートフィルを活用しちゃおうシリーズ」の最後に、少し難易度の高いやつをご紹介します。ここまでの解説で物足りなさを感じていた方は、ぜひ実装してみてください。それは、連続データを作る場所のところで触れた

作業中のワークシートで、選択中のセル範囲に作成する
非表示のワークシートを作って、そこに作成する

の、2.です。マクロの中で非表示シートを作り、そこで連続データを作成し、作成した連続データを選択範囲に代入するという操作です。この発想を使うと、次のようなことが可能になります。

手動操作では不可能な、こんなことや、

あんなことや、

非連続のセルに入れることも。

非連続のセルに連続データを代入できるということは、オートフィルタで絞り込んだ結果だけに連続データを代入することだってできちゃいます。これ、実務ではよくやるのでしょうね。これをやりたいという質問がメチャクチャ多いです。

では、解説します。なお、ここでは説明を簡単にするため、次のような手順をケースとします。実際には、これを自由に応用してください。

元データはアクティブシート
非表示シートを作成する
選択範囲の先頭セルに入力されている値を非表示シートにコピーする
非表示シートで連続データを作成する
作成した連続データをアクティブシートの選択範囲に代入する
非表示シートを削除する

まずは、非表示シートを作成するところです。これに関しては、下記ページで"考え方"と"やり方"を詳しく解説していますので、そちらをご覧ください。

非表示のシートを挿入する

Sub Macro9()
    Sheets.Add.Hidden = True
End Sub

アクティブシートで選択されているセル範囲(Selection)の、先頭セル(Selection(1))を、非表示シートのセルA1にコピーします。

Sub Macro9()
    Sheets.Add.Visible = False
    With ActiveSheet.Previous
        Selection(1).Copy .Range("A1")
    End With
End Sub

非表示シートで連続データを作成します。

Sub Macro9()
    Sheets.Add.Visible = False
    With ActiveSheet.Previous
        Selection(1).Copy .Range("A1")
        .Range("A1").AutoFill .Range("A1").Resize(Selection.Count)
    End With
End Sub

ResizeやSelection.Countに関しては、上述していますので、イメージできない方は読み直してください。続いて、作成した連続データを、アクティブシートの選択しているセル範囲に代入します。

Sub Macro9()
    Dim i As Long
    Sheets.Add.Visible = False
    With ActiveSheet.Previous
        Selection(1).Copy .Range("A1")
        .Range("A1").AutoFill .Range("A1").Resize(Selection.Count)
        For i = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
            Selection(i) = .Cells(i, 1)
        Next i
    End With
End Sub

For Nextの変数iが、なぜ「2」からなのか分かりますか？先頭のセルは最初にコピーしたので、先頭のセルに代入する必要はないからです。最後に、非表示シートを削除します。

Sub Macro9()
    Dim i As Long
    Sheets.Add.Visible = False
    With ActiveSheet.Previous
        Selection(1).Copy .Range("A1")
        .Range("A1").AutoFill .Range("A1").Resize(Selection.Count)
        For i = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
            Selection(i) = .Cells(i, 1)
        Next i
        Application.DisplayAlerts = False
        .Delete
        Application.DisplayAlerts = True
    End With
End Sub

こんな感じです。先頭セルを"コピー"して、作成した連続データは"代入"していますが、これはケースバイケースです。実際には、書式を反映させるかどうかなどによって検討してください。