Excelのオートフィル機能をVBAから実行し、
セルのコピーや連続データの作成を行う方法を解説します。
RangeオブジェクトのAutoFillメソッドを使用します。
基本形
始点セル.AutoFill Destination:=出力セル範囲, Type:=オートフィル設定 ' 省略形 始点セル.AutoFill 出力セル範囲, オートフィル設定
実行例
' A1セルの内容をA1:A5までコピー Range("A1").AutoFill Range("A1:A5"), xlFillCopy ' A1セルの値のみをA1:A5までコピー Range("A1").AutoFill Range("A1:A5"), xlFillValues ' A1からA100セルまでに連番を出力 Range("A1").Value = 1 Range("A1").AutoFill Range("A1:A100"), xlFillSeries ' A1セルからA12セルまでに2025年の各月1日を出力 Range("A1").Value = DateSirial(2025, 1, 1) Range("A1").AutoFill Range("A1:A12"), xlFillMonths
解説
RangeオブジェクトのAutoFillメソッドを使用することで、
Excelにおける「オートフィル」と同じ操作をVBAで実行できます。
第2引数「Type」はExcelのフィルハンドルダイアログと対応しており、
以下の値を設定可能です。
| 定数名 | 値 | 内容 |
|---|---|---|
| xlFillDefault | 0 | Excelによる自動判定 |
| xlFillCopy | 1 | セルのコピー |
| xlFillSeries | 2 | 連続データ |
| xlFillFormats | 3 | 書式のみコピー |
| xlFillValues | 4 | 書式なし(値のみ)コピー |
| xlFillDays | 5 | 連続データ(日単位) |
| xlFillWeekdays | 6 | 連続データ(平日のみ) |
| xlFillMonths | 7 | 連続データ(月単位) |
| xlFillYears | 8 | 連続データ(年単位) |
| xlLinearTrend | 9 | 連続データ(加算推定) |
| xlGrowthTrend | 10 | 連続データ(乗法推定) |
| xlFlashFill | 11 | フラッシュフィル |
上記のうち、日付系列の連続データ実行例は以下の通りです。
| xlFillDays | xlFillWeekdays | xlFillMonths | xlFillYears |
|---|---|---|---|
| 4/15 | 4/15 | 4/15 | 2025/4/15 |
| 4/16 | 4/16 | 5/15 | 2026/4/15 |
| 4/17 | 4/17 | 6/15 | 2027/4/15 |
| 4/18 | 4/18 | 7/15 | 2028/4/15 |
| 4/19 | 4/21 | 8/15 | 2029/4/15 |
| 4/20 | 4/22 | 9/15 | 2030/4/15 |
| 4/21 | 4/23 | 10/15 | 2031/4/15 |
| 4/22 | 4/24 | 11/15 | 2032/4/15 |
| 4/23 | 4/25 | 12/15 | 2033/4/15 |
| 4/24 | 4/28 | 1/15 | 2034/4/15 |
| 4/25 | 4/29 | 2/15 | 2035/4/15 |
| 4/26 | 4/30 | 3/15 | 2036/4/15 |
第2引数省略時は「0：Excel自動判定」が指定されたとみなされます。
Excelで手作業実行する時と同様、
Range("A1:A2").AutoFill Range("A1:A10")
このように始点を複数のセルにしておくことで、
A1とA2の関係でコピーor連続データを自動判定させることもできます。
が、せっかくVBAで組むのに実行内容をExcelに推定させる必要もないので、
基本は第2引数もしっかり指定して実行することをおすすめします。
AutoFillメソッドの処理速度
AutoFillメソッドの有用な点として、処理速度が高速です。
AutoFillメソッドで実行する処理はどれも単純なFor文で代替可能ですが、
For文で1セルずつ出力するのに比べて数百～数千倍の速度が出ます。
特に大量の連番出力などで速度に困った場合は、
AutoFillメソッドの利用を検討してください。
※ より正確に、AutoFillメソッドが高速である理由は、
セル(Rangeオブジェクト)へのアクセス回数が1回で済むためです。
For文で連番配列を生成してからセルに出力するコードとほぼ同速と捉えてください。
※ Excelのバージョンが対応しているのであれば、
WorksheetFunction.Sequenceともほぼ同速です。
Resizeプロパティとの併用
フィルさせるセルの個数が決まっている場合は、
以下のようにResizeプロパティを活用するとコードがスッキリします。
' 1～100の連番を出力 始点セル.Value = 1 始点セル.AutoFill 始点セル.Resize(100), xlFillSeries ' A1セルからA12セルまでに2025年の各月1日を出力 始点セル.Value = DateSirial(2025, 1, 1) 始点セル.AutoFill 始点セル.Resize(12), xlFillMonths
100個の連番や12ヶ月など、出力するセルの個数の方が決まっている場合は、
Resizeプロパティを有効活用してください。
AutoFillメソッドがエラーとなるパターン
このメソッドは意外とエラーになるパターンが少なく、
たとえばExcelでユーザー操作では実行できないような、
Range("A1").AutoFill Range("A1:B5")
この実行をしても、以下のような不思議な結果になります。
また、日付でない数値に対して日付系の連続データを指定した場合は、
ただのコピーとして動きます。
Range("A1").Value = 1 Range("A1").AutoFill Range("A1:A12"), xlFillMonths ' ↑「1」に対して「月ごと」を指定するとただのコピーとして扱われる(全セルに1が入る)
意図しない処理を書いてしまってもエラーが出てくれないということなので、
注意して使用してください。
はっきりとエラーになる主なパターンとしては、
① 出力範囲が矩形ではない(Areasが1つではない)場合
Range("A1").AutoFill Range("A1,A3,A5"), xlFillCopy
② 始点セルと出力範囲が別シートである場合
Worksheets(1).Range("A1").AutoFill Worksheets(2).Range("A1:A5"), xlFillCopy
この2つが該当し、いずれも
| RangeクラスのAutoFillメソッドが失敗しました。 |
エラーとなります。
AutoFillメソッドでエラーが発生した際は、
セル範囲の取得が正しいかをまずは確認してください。
