以下のとおり実験してみました。
参考にしていただきたく思います。
■イベント処理ではなく、一括処理として、同様の処理を
(1)テーブルを使用しない
(2)テーブルを使用する （セル操作には、ListObjectを利用しない。）
(3)テーブルを使用する （セル操作には、ListObjectを利用する。）
の３つの方法で実行して、時間を計測しました。。
■処理内容は概略次のとおりです。
・A1から始まる縦50000行、横9列のセル範囲を対象とする。
・45000行までは
7列目に日付をいれ、9列目に担当者コードを入れます。
・45001行目以降の5000行に対して、
日付、担当者コードに関して、前行のデータを増幅する処理としました。
・なお、6列目までについては、50000行までダミーの数式を入れてあります。
■結果
(1) 0.3828125秒 テーブルなし
(2) 0.5585938秒 テーブルあり
(3) 0.5507813秒 ListObject利用
もう一回
(1) 0.375 秒 テーブルなし
(2) 0.5703125秒 テーブルあり
(3) 0.5429688秒 ListObject利用
となりました。解釈は皆さまにお任せいたします。
入力は、テーブルなしの状態で行い、データ入力後にテーブル化してから、
PowerQuery等を使ってデータ操作するのが、今どきかもしれませんね。
以下参考までに使用したコードを載せておきます。
Dim ws As Worksheet
Dim myRange As Range
Sub Table利用せず()
Dim k&
Dim t
Set ws = Sheet1
Set myRange = ws.[A1].Resize(50000, 9)
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Call 初期設定
With ws
t = Timer ' ここ以降の実行時間を測定
For k = 45001 To 50000
If .Cells(k, 7).Value = "" Then
.Cells(k, 7).Value = .Cells(k - 1, 7).Value
If .Cells(k, 9).Value = "" Then
.Cells(k, 9).Value = .Cells(k - 1, 9).Value
End If
End If
Next
End With
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Debug.Print Timer - t; " テーブルなし"
End Sub
Sub Table利用()
Dim k&
Dim t
Set ws = Sheet1
Set myRange = ws.[A1].Resize(50000, 9)
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Call 初期設定
With ws
.ListObjects.Add(xlSrcRange, myRange, , xlYes).Name = "テーブル1"
t = Timer
For k = 45001 To 50000
If .Cells(k, 7).Value = "" Then
.Cells(k, 7).Value = .Cells(k - 1, 7).Value
If .Cells(k, 9).Value = "" Then
.Cells(k, 9).Value = .Cells(k - 1, 9).Value
End If
End If
Next
End With
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Debug.Print Timer - t; " テーブルあり"
End Sub
Sub ListObject利用()
Dim listObj As ListObject
Dim k&
Dim t
Set ws = Sheet1
Set myRange = ws.[A1].Resize(50000, 9)
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Call 初期設定
With ws
.ListObjects.Add(xlSrcRange, myRange, , xlYes).Name = "テーブル1"
Set listObj = .ListObjects("テーブル1")
t = Timer ' ここ以降の実行時間を測定
For k = 45001 To 50000
If listObj.ListRows(k - 1).Range(7) = "" Then
listObj.ListRows(k - 1).Range(7) = listObj.ListRows(k - 2).Range(7)
If listObj.ListRows(k - 1).Range(9) = "" Then
listObj.ListRows(k - 1).Range(9) = listObj.ListRows(k - 2).Range(9)
End If
End If
Next
End With
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Debug.Print Timer - t; " ListObject利用"
End Sub
Function 初期設定()
myRange.ClearContents
On Error Resume Next
If ws.ListObjects.Count > 0 Then
ws.ListObjects(1).Delete
End If
On Error GoTo 0
'ダミーデータをセット
ws.[A1].Resize(50000, 6).Formula = "=row() & ""_"" & column()"
ws.[G1:G45000] = DateSerial(2022, 10, 1)
ws.[I1:I45000] = "A100"
End Function
