長時間処理時の大体のバターン、再利用のためメモした、高速化、画面更新停止、再計算停止

Dim startTime As Date
Dim usedTime As Long
startTime = Now()

Application.Calculation = xlCalculationManual 'これは必要に応じで
Application.ScreenUpdating = False

for each item in items
    DoEvents
    'do things
next

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic 'これは必要に応じで
Application.Calculate

usedTime = DateDiff("s", startTime, Now())
MsgBox "完了しました。" & vbNewLine & vbNewLine & "経過時間：" & usedTime & "秒", vbInformation + vbOKOnly, "処理結果"