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

任意のセルに「1月」と入力します。そのセルのフィルハンドルをドラッグすると「2月」「3月」…という連続データを作成できます。セミナーで教えると、ほとんどのビギナーから感嘆の声が上がる鉄板ネタです。これをマクロで実行するには次のようにします。次のコードは、セル範囲B2:B9に「1月」～「8月」の連続データを挿入します。

Sub Sample1()
    With Range("B2")
        .Value = "1月"
        .AutoFill Destination:=Range("B2:B9")
    End With
End Sub

マクロで連続データを作成するには、RangeオブジェクトのAutoFillメソッドを使うのですが、ワークシート上の手作業と違い、マクロではどこに連続データを作成するか…というより、いくつの連続データを作成するかといった考え方が一般的です。上記のコードでは、セル範囲B2:B9に連続データが作成されますが、これを8個の連続データと考えるのなら、次のように書いた方が直感的でしょう。

Sub Sample2()
    With Range("B2")
        .Value = "1月"
        .AutoFill Destination:=.Resize(8)
    End With
End Sub

Resize(8)は、正確にはResize(8,1)です。これは、1行×1列であるセルB2を、8行×1列のセル範囲B2:B9に変形せよという命令です。詳しくはResizeメソッドのヘルプを読んでください。

連続データをUserFormから使う応用例をひとつご紹介しましょう。まず、次のようなUserFormをデザインします。

上には、連続データの元になる文字列を入力するTextBoxを配置し、その下には作成した連続データを作成するCommandButtonを置きます。作成した連続データはListBoxに表示します。
作成する連続データの個数は5個としました。定数nを使っていますので、個数を変更するときは定数の値を変えてください。

Private Sub CommandButton1_Click()
    Const n As Long = 5                     ''作成する連続データの個数
    If TextBox1 = "" Then Exit Sub          ''元になるデータが空欄だったら終了
    With Range("A1")                        ''アクティブシートのセルA1
        .Value = TextBox1
        .AutoFill Destination:=.Resize(n)   ''連続データを作成
        ListBox1.List = .Resize(n).Value    ''作成した連続データをListBoxに登録
        .Resize(n).ClearContents            ''作成した連続データを削除
    End With
End Sub

上記のコードでは、アクティブシートのセルA1から下方向に連続データを作成しています。もしアクティブシートを作業用として使用できないのでしたら、次のように作業用ワークシートを一時的に挿入するといいでしょう。

Private Sub CommandButton1_Click()
    Const n As Long = 5                     ''作成する連続データの個数
    If TextBox1 = "" Then Exit Sub          ''元になるデータが空欄だったら終了
    Application.ScreenUpdating = False      ''★画面の更新を抑止
    Worksheets.Add                          ''★新しいワークシートを挿入
    With Range("A1")                        ''挿入したワークシートのセルA1
        .Value = TextBox1
        .AutoFill Destination:=.Resize(n)   ''連続データを作成
        ListBox1.List = .Resize(n).Value    ''作成した連続データをListBoxに登録
        .Resize(n).ClearContents            ''作成した連続データを削除
    End With
    Application.DisplayAlerts = False       ''★確認メッセージを抑止
    ActiveSheet.Delete                      ''★挿入したワークシートを削除
    Application.DisplayAlerts = True        ''★確認メッセージの表示を許可
    Application.ScreenUpdating = True       ''★画面の更新を許可
End Sub

★印が追加した行です。毎回新しいワークシートを挿入して連続データを作り、終わったらそのワークシートを削除するなんて、処理に時間がかかるのでは…とご心配の方は、実際に試してください。画面の更新を抑止していますので、それらはメモリ上での出来事です。処理の速度に大きな差は生じませんよ。