EXCEL2003 VBAで動作が速くなるようにマクロ記述したいのです

質問

EXCEL2003　VBAで動作が速くなるようにマクロ記述したいのですが、どのように行えばいいのでしょうか？

Sheet1のA1からA300まで、関数によって計算されたデータが格納されています。
そのA1からA300の値（関数の計算結果のみ）を、コマンドボタンをクリックした時にSheet2のA1からA300にコピーしています。
コマンドボタンをクリックする度に、Sheet1のA1からA300までの値を、Sheet2に列を変えてコピーし、値を蓄積する方法を取っています。

以下のマクロを記述して走らせてみましたが、動作が遅いのが気になります。
コピーして貼り付けている動作が遅くなっているのでしょうか？
もう少し早くなる方法はありますでしょうか？
よろしくお願いします。

Sub CommandButton1_Click1()
Dim I
Dim N

Worksheets("sheet1").Range("F1").Value = Range("F1").Value + 1
N = Worksheets("sheet1").Range("F1").Value

For I = 1 To 300

　Application.ScreenUpdating = False

　　Worksheets("sheet1").Cells(I, 1).Copy
　　Worksheets("sheet2").Cells(I, N).PasteSpecial Paste:=xlValues

　Application.ScreenUpdating = True

Next

End Sub

回答No.5

okormazd · ベストアンサー

forで回さなければいないのですか。
単に領域のコピペに見えますが。
それでいいなら，下記でどうですか，ずっと速いですよ。
なお，R1C1形式にそろえました。

Sub CommandButton1_Click1()

Dim i, i0
Dim n, sh1, sh2

Set sh1 = Worksheets("sheet1")
Set sh2 = Worksheets("Sheet2")

With sh1
.Cells(1, 6).Value = .Cells(1, 6).Value + 1

n = .Cells(1, 6).Value

Application.ScreenUpdating = False

i0 = 1
i = 300

Range(.Cells(i0, 1), .Cells(i, 1)).Copy
sh2.Cells(i0, n).PasteSpecial Paste:=xlValues

Application.ScreenUpdating = True

End With

End Sub

お礼

仰るとおりForで回す必要性を考えたところ入りませんでした。

ホントに早いですね。
一瞬で結果表示されます。

拡張性も高そうなマクロなので大変勉強になりました。

ありがとうございました。

回答No.6

Wendy02

Sub CommandButton1_Click1()
　Dim N As Long
　N = Worksheets("Sheet1").Range("F1").Value + 1
　Worksheets("Sheet1").Range("F1").Value = N
　　Worksheets("Sheet2").Cells(1, N).Resize(300).Value = _
　　Worksheets("Sheet1").Cells(1, 1).Resize(300).Value
End Sub

必要に応じて、これで挟む必要があるかもしれませんが、それは結果をみて決めればよいと思います。

Application.Calculation = xlManual
　・
　・
Application.Calculation = xlAutomatic

Application.ScreenUpdating = False　は、シートの上をループして動かなければ不要ですが、試してみてから決めてください。

お礼

.Valueでコピーする方法もあるのですね。
早いですね！

大変参考になりました。
ありがとうございましたm(__)m

回答No.4

hananoppo

これでいいのでは？

Private Sub CommandButton1_Click()
Dim n As Integer

Application.ScreenUpdating = False
n = Worksheets("Sheet1").Range("F1").Value + 1
Worksheets("Sheet1").Range("F1").Value = n
Worksheets("Sheet1").Range("A1:A300").Copy
Worksheets("Sheet2").Cells(1, n).PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

お礼

一瞬で結果表示されます!!

マクロの記述の仕方で全く処理スピードが変わるもんなんですね。
ビックリしました。

ありがとうございました。

回答No.3

conanthe

こんにちは。ハンドルネームconantheです。

おそらくシートに変更が加えられるたびに再計算が行われるので遅くなっていると思われます。プロシージャの最初と最後に、次のコマンドを入れてみてください。最初のコマンドで再計算をオフにし、最後のコマンドでオンにします。

Sub CommandButton1_Click1()
Application.Calculation = xlManual
・
・
・
Application.Calculation = xlAutomatic
End Sub

お礼

自動再計算の停止＆再開というコマンドもあるのですね。

参考になりました。
ありがとうございましたm(__)m

回答No.2

bin-chan

＃１ですが、申し上げたかったのは以下の２点。
１）For文中のScreenUpdatingの設定をFor文外で行う。
２）代入式の見直し

２）はともかく、１）は変更必須です。

お礼

For文中のScreenUpdatingの設定をFor文外で行うだけで全然処理スピードが違いますね。

これは一つ勉強になりました。
ありがとうございましたm(__)m

回答No.1

bin-chan

Application.ScreenUpdating = False

For I = 1 To 300
　　Worksheets("sheet2").Cells(I, N).value = Worksheets("sheet1").Value
Next

Application.ScreenUpdating = True

でどうなります？