以前、エクセルのワークシートにVBAで10万行分くらいのセルに連番を入力していこう～としたら、えらい時間がかかった。パソコンのスペックがダメダメな会社のパソコンというのもあるだろうけど、手でやったら数秒なのに！
その時に知ったのですが、VBAでワークシートのセルにアクセスする、というところが思いのほか時間がかかるらしいです。
で、結局VBAでオートフィルを使ったやり方が一番簡単早いだな、と思ったのですが、それも含め、その他連番を振るVBAを３種類こちらに記載します。
１．Autofillメソッドを利用した連番入力マクロ（おすすめ！）
こちらが一番おすすめです。簡単かつ早いです。
（１）AutoFillメソッドを使って連番を入力するコード
以下、アクティブシートのセルA１～A1000000まで百万行に№1～百万まで連番を入力するコードです。
Sub renban()
Range("A1").Value = 1
Range("A1").AutoFill Destination:=Range("A1:A1000000"), Type:=xlFillSeries
End Sub実行時間を図りました。0.2734375秒。1秒もかかりませんね！
コードの説明です。
①ワークシートのセルA1に「1」を入力します
Range("A1").Value = 1②AutoFillメソッドでセルA1～A1000000に連番を入力します。
手でやると、セルA1を選択してぐーーーっと百万行下までドラッグしていくイメージ。
Range("A1").AutoFill Destination:=Range("A1:A1000000"), Type:=xlFillSeries（２）AutoFillメソッドについて
AutoFillメソッドについて解説します。
Rangeオブジェクト.AutoFill(Destination, Type)
Rangeオブジェクトには起点となるセル（またはセル範囲）を指定します。
引数1:Destination(必須）
Destinationには連番を書き込むセル範囲を指定します。
※起点となるセル範囲を含めて指定します
引数2：Type（省略可）
連続データを作成する場合はxlFillSeriesを指定します。
以下、OfficeTANAKA先生のサイトより引用させていただきました。
| 定数 | 値 | 説明 |
|---|---|---|
| xlFillDefault | 0 | 何を埋め込むかExcelが判断します(既定値) |
| xlFillCopy | 1 | セルをコピーします |
| xlFillSeries | 2 | 等間隔の数値を埋め込みます |
| xlFillFormats | 3 | 書式のみを適用します |
| xlFillValues | 4 | 書式を適用せず、値のみを埋め込みます |
| xlFillDays | 5 | “日”を連続データの対象にします |
| xlFillWeekdays | 6 | “週日”を連続データの対象にします |
| xlFillMonths | 7 | “月”を連続データの対象にします |
| xlFillYears | 8 | “年”を連続データの対象にします |
| xlLinearTrend | 9 | 加算した数値を埋め込みます |
| xlGrowthTrend | 10 | 乗算した数値を埋め込みます |
（３）シートを指定した場合のコード
シートを指定した場合の書き方はこちら
Sub renban2()
With Worksheets(1)
.Range("A1").Value = 1
.Range("A1").AutoFill Destination:=.Range("A1:A1000000"), Type:=xlFillSeries
End With
End SubポイントはWith WorkSheets(1)としているので、With構文の中のRangeの前にドット(.)を忘れずにつけます。
（４）変数を利用した場合のコード
■最終行を変数にした場合の書き方の例です。
Sub renban3()
Dim rowEnd As Long
rowEnd = 1000000
With Worksheets(1)
.Range("A1").Value = 1
.Range("A1").AutoFill Destination:=.Range("A1:A" & rowEnd), Type:=xlFillSeries
End With
End Sub最終行を変数rowEndで指定します。AutoFillのDestinationで「Range（”A1:A”」の後に&で変数rowEndを繋げて、「Range（”A1:A” & rowEnd)」とします。
■開始行、最終行、列もすべて変数にした場合の書き方の例です。
Sub renban4()
Dim rowTop As Long, rowEnd As Long, trgCol As Long
rowTop = 1 '連番入力開始行
rowEnd = 1000000 '連番入力最終行
trgCol = 1 '連番を入力する列
With Worksheets(1)
.Cells(rowTop, trgCol).Value = 1
.Cells(rowTop, trgCol).AutoFill Destination:=.Range(.Cells(rowTop, trgCol), .Cells(rowEnd, trgCol)), Type:=xlFillSeries
End With
End Sub変数rowTopに開始行を代入、rowEndに終了行を代入します。trgColには入力する列を番号で指定します。例ではA列に入力したいので「１」を指定しています。B列の場合は「２」を指定します。
開始セルをCellsを使って指定します。
.Cells(行,列）と指定します。
なので、
.Cells(rowTop, trgCol)
と指定します。
AutoFillメソッドの起点は開始セル.Cells(rowTop, trgCol)と指定します。
Destinationには Rangeでセル範囲を指定、Cellsで開始セルと終了セルを指定します。RangeとCellsを合わせて使い、.Range(開始セル,終了セル)と指定。
.Range(.Cells(rowTop,trgCol),.Cells(rowEnd,trgCol))
With構文の中に入れている場合、RangeとCellsの前のドットを忘れずに！
（５）起点にセル範囲を指定した場合
起点にセル範囲を指定することも可能です。
以下の例のように、セルA1に「2」セルA2に「4」と入力してあり、セルA1～A2を範囲指定してオートフィルをかけると2,4,6,8,10・・・と偶数の連番になりますよね。
Sub renban5()
'起点にセル範囲を指定
Range("A1:A2").AutoFill Destination:=Range("A1:A10"), Type:=xlFillSeries
End Sub
２．For文で一つ一つのセルに連番を入力
これは大量セルへの書き込みの場合、時間がかかります。そんなに時間は気にしないという場合や、大量でない場合は、これが一番わかりやすい書き方かもしれません。
Sub renban6()
Dim t As Double
t = Timer '時間計測
'A列の1行から百万行まで、1～連番を入力する
Dim i As Long
For i = 1 To 1000000
Cells(i, 1).Value = i
Next i
'時間をイミディエイトウインドウに出力
Debug.Print Timer - t & "秒"
End Subついでに時間を計測しました。47.16015625秒。結構かかりますね。
前後に Application.ScreenUpdating = False/True の切り替えを入れてみたところ、45.95703125秒と若干早くなりました(一回ずつしか計ってないけど）
ちなみに時間計測ではTimer関数を使っています。
Timer関数は、0時から計測した現在の経過秒数をミリ秒単位で取得するそうです。
最初に変数tにTimer関数でマクロ開始時の0時からの経過秒数を代入します。
マクロ終了時にTimer関数で再度現在の経過秒数を取得して、開始時の秒数tを引くことで、マクロ実行にかかった秒数をミリ秒単位で取得することができます。
For文で連番を入力するコードは解説の必要もないかと思いますので割愛します。
ちなみに2行目から開始し、連番を別の変数に持つ場合の書き方の例はこちらです。
Sub renban7()
Application.ScreenUpdating = False
'A列の2行目から、1～百万までの連番を入力する
Dim i As Long
Dim no As Long: no = 1
For i = 2 To 1000001
Cells(i, 1).Value = no
no = no + 1
Next i
Application.ScreenUpdating = True
End Sub３．配列に連番を作成し、一気にワークシートに出力する方法
こちらも早いです。でもちょっとややこしいかもしれません。
まず、配列にシートに書き込みしたい連番を作成しておきます。
そのうえでその配列を一気にワークシートに出力します。
「セル一つ一つに書き込みすると時間がかかる」を解消するために配列を使ってセルに一気に出力する方法を採用したものです。
配列を一気に出力方法について詳しくはこちらをどうぞ＝＞【Excel/VBA】高速！ワークシートに出力するにはデータを配列に入れて一気に出力がおススメ！
（１）2次元配列に連番を作成し、一気に出力するコード
前項までと同様に、セルA1～A1000000に連番1～百万を入力するコード
Sub renban8()
'百万までの連番を格納するための配列を用意
Dim arr(999999, 0) As Long
'配列インデックスの変数。配列は0始まり。
Dim idx As Long: idx = 0
'連番のための変数no
Dim no As Long
'配列arrに連番を作成して代入
For no = 1 To 1000000
arr(idx, 0) = no
idx = idx + 1 '配列インデックスを1プラス
Next no
'配列arrをシートのセルA1～A1000000に出力
Range("A1:A1000000").Value = arr
End Subコードを順を追って説明します。
①連番を格納するための配列を宣言します。
'百万までの連番を格納するための配列を用意
Dim arr(999999, 0) As Longポイントは１次元配列arr(999999)とはせず、２次元配列の形arr(999999,0)としているところです。２次元目に0と指定しているので、1列ではあるのですが。
1次元配列の場合、横に伸びていくので、ワークシートに出力するときに縦横変換しなければいけなくなるため、2次元配列の形にしています。2次元配列で1次元目を百万行分確保しておくことで縦長の配列になります。なお、配列は0始まりにしているので、1000000から1引いた999999を1次元目に指定しています。これで百万行、1列の配列を確保したことになります。
下図はマクロを途中で止めて、配列arrをローカルで見たものです。
②配列インデックスの変数idxを用意。0を代入しておきます。
連番のための変数noも用意しておきます。
'配列インデックスの変数。配列は0始まり。
Dim idx As Long: idx = 0
'連番のための変数no
Dim no As Long③配列arrに連番を代入していきます。
For文でno1～1000000まで繰り返し処理します。
配列arrにnoを代入します。
noは自動で1プラスされていきます。idxは1ずつプラスしていきます。
'配列arrに連番を作成して代入
For no = 1 To 1000000
arr(idx, 0) = no
idx = idx + 1 '配列インデックスを1プラス
Next no④配列arrをワークシートA1～A1000000に一気に出力します。
配列が縦型なので、そのまま出力すればOKです。
'配列arrをシートのセルA1～A1000000に出力
Range("A1:A1000000").Value = arr（２）変数を利用した場合のコード
以下は、開始番号と最終番号を変数にした場合の書き方の例です。
1～100までの連番を1番目のシートのA列に出力します。
Sub renban9()
'連番開始№、最終№
Dim noTop As Long, noEnd As Long
noTop = 1 '開始№
noEnd = 100 '最終№
'連番を格納するための配列を用意
Dim arr() As Long
'配列を再宣言。連番数分の領域を確保
ReDim arr(noEnd - noTop, 0)
'配列インデックスの変数。配列は0始まり。
Dim idx As Long: idx = 0
'連番のための変数no
Dim no As Long
'配列arrに連番を作成して代入
For no = noTop To noEnd
arr(idx, 0) = no
idx = idx + 1 '配列インデックスを1プラス
Next no
'配列arrをシートのセルA1～A1000000に出力
With Worksheets(1)
.Range(.Cells(1, 1), .Cells(UBound(arr) + 1, 1)).Value = arr
End With
End Sub（３）1次元配列を利用した場合のコード
以下は、一次元配列を利用した場合の書き方の例です。
変数noEndに連番の最終番号を代入。この例では1～100までの連番を作成し、A列に出力します。
For文の中でReDimして要素数を増やしながら、連番を格納しています。
この時ReDimの後に「Preserve」を忘れずに！「Preserve」を入れないとそれまでに格納した要素がきえてしまいます。
※後述しますが、6万行を超えるような大量のデータではこの方法は利用できません。
Sub renban10()
'最終番号
Dim noEnd As Long: noEnd = 100
'連番のための変数no
Dim no As Long
'連番を格納するための配列を用意
Dim arr() As Long
'配列インデックスの変数。配列は0始まり。
Dim idx As Long: idx = 0
'配列arrに連番を作成して代入（※１）
For no = 1 To noEnd
ReDim Preserve arr(idx) '配列を再宣言して増やす
arr(idx) = no
idx = idx + 1 '配列インデックスを1プラス
Next no
'配列arrをシートのセルA1～A100に出力（※２）
Range("A1:A" & UBound(arr) + 1).Value = WorksheetFunction.Transpose(arr)
End Sub※1：For文の中でReDimして要素数を増やしながら、連番を格納しています。
この時ReDimの後に「Preserve」を忘れずに！「Preserve」を入れないとそれまでに格納した要素がきえてしまいます。
'配列arrに連番を作成して代入
For no = 1 To noEnd
ReDim Preserve arr(idx) '配列を再宣言して増やす
arr(idx) = no
idx = idx + 1 '配列インデックスを1プラス
Next no※２：配列arrはそのまま出力すると横方向に出ます。なので、Transposeして縦横変換して出力します。
なお、WorkSheetFunction.Transposeで配列を縦横変換した場合、65,536行を超える場合はおかしくなるため、65,536を超える場合は利用できません。
詳しくはこちら＞＞＞【Excel/VBA】配列をTransposeしたらちょん切れたので検証と回避方法を模索
'配列arrをシートのセルA1～A100に出力（※２）
Range("A1:A" & UBound(arr) + 1).Value = WorksheetFunction.Transpose(arr)配列を一気に出力する方法、Transpose縦横変換の限界については以下の記事もご参考としてください。
【Excel/VBA】高速！ワークシートに出力するにはデータを配列に入れて一気に出力がおススメ！
【Excel/VBA】配列をTransposeしたらちょん切れたので検証と回避方法を模索
なお、利用したパソコンの仕様は以下です。
デバイス名 DESKTOP-M6EIU5C
プロセッサ Intel(R) Core(TM) i5-1035G1 CPU @ 1.00GHz 1.19 GHz
実装 RAM 8.00 GB (7.78 GB 使用可能)
システムの種類 64 ビット オペレーティング システム、x64 ベース プロセッサ
Excel：Microsoft365
2022.7.24.加筆修正
お読みいただきありがとうございました！
コメント