質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.50%
VBA

VBAはオブジェクト指向プログラミング言語のひとつで、マクロを作成によりExcelなどのOffice業務を自動化することができます。

ループ

ループとは、プログラミングにおいて、条件に合致している間、複数回繰り返し実行される箇所や、その制御構造を指します

最適化

最適化とはメソッドやデザインの最適な処理方法を選択することです。パフォーマンスの向上を目指す為に行われます。プログラミングにおける最適化は、アルゴリズムのスピードアップや、要求されるリソースを減らすことなどを指します。

Office 365

Office 365は、マイクロソフトが販売している企業向けクラウドベースのグループウエアサービス。電子メールや予定表、Webサイト構築、オンラインストレージ、ビデオ会議などビジネスで必要な機能を備えています。クラウドサービスのため、自社での専用サーバーの設置の必要がないことが特徴です。

Q&A

解決済

2回答

10248閲覧

VBA - Doループ+各行をコピペするマクロを高速化したい

koyamashinji

総合スコア45

VBA

VBAはオブジェクト指向プログラミング言語のひとつで、マクロを作成によりExcelなどのOffice業務を自動化することができます。

ループ

ループとは、プログラミングにおいて、条件に合致している間、複数回繰り返し実行される箇所や、その制御構造を指します

最適化

最適化とはメソッドやデザインの最適な処理方法を選択することです。パフォーマンスの向上を目指す為に行われます。プログラミングにおける最適化は、アルゴリズムのスピードアップや、要求されるリソースを減らすことなどを指します。

Office 365

Office 365は、マイクロソフトが販売している企業向けクラウドベースのグループウエアサービス。電子メールや予定表、Webサイト構築、オンラインストレージ、ビデオ会議などビジネスで必要な機能を備えています。クラウドサービスのため、自社での専用サーバーの設置の必要がないことが特徴です。

0グッド

0クリップ

投稿2020/12/11 08:20

編集2020/12/11 08:21

VBA - Doループ+各行をコピペするマクロを高速化したい

以下のマクロを作成しましたが、50行程度あるシートに対し処理を実行~完了するまで約10秒程度かかってしまいます。
シンプルなコードなので、なぜここまで時間がかかってしまうのか、、悩んでいます。

今後、対象データが1000件以上になるケースが想定されるため、処理速度をより高速化したく。
改善方法をご助言頂けますと幸いです。

ちなみに、コード中に記載のとおり、以下はすでに試しました。

  • Application.ScreenUpdating = Falseにして処理を実行する
  • 貼付け時、「値のみの貼付け」をする .PasteSpecial Paste:=xlPasteValues

OSはWindows 10、
Excelバージョンは、Microsoft Excel for Office 365 64bit です

マクロの内容

シートws2のA列セルの値(仮にcells(j, "A")とする)の中で、シートws1のA列セルの値(仮にcells(i, "A")とする)と一致するものがあるとき、
range("A" & j, "BA" & j)をRange("A" & i, "BA" & i)にコピペする
一致するものがない場合のみ、range("A" & j, "BA" & j)をシートws1の一番下の空欄列にコピペする

コード

Public ws1 As Worksheet Sub test() Dim ws2 As Worksheet Set ws1 = ThisWorkbook.Worksheets("ws1") Set ws2 = ThisWorkbook.Worksheets("ws2") Dim i As Double Dim j As Double Dim lastRow As Double Dim updated As Boolean Application.ScreenUpdating = False i = 2 Do Until ws2.Cells(i, "A").Value = "" updated = False j = 5 Do Until ws1.Cells(j, "A").Value = "" If ws2.Cells(i, "A").Value = ws1.Cells(j, "A").Value Then ws2.Range("A" & i, "BA" & i).Copy ws1.Range("A" & j).PasteSpecial Paste:=xlPasteValues updated = True Exit Do End If j = j + 1 Loop If updated = False Then lastRow = ws1.Cells(4, "A").End(xlDown).Row ws2.Range("A" & i, "BA" & i).Copy ws1.Range("A" & lastRow + 1).PasteSpecial Paste:=xlPasteValues End If i = i + 1 Loop Application.ScreenUpdating = True End Sub

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

guest

回答2

0

ベストアンサー

50行ぐらいで10秒かかるとすると、セルに関数が多数設定されていませんか。
更新するたびに再計算が実行されるので重くなります。
自動計算を停止すると改善するかもしれません。

vba

1Application.ScreenUpdating = False 2Application.Calculation = xlCalculationManual ' 手動計算にする 3 4'ループ処理 5 6Application.Calculation = xlCalculationAutomatic ' 自動計算にする 7Application.ScreenUpdating = True

これで改善しないなら、Copy と PasteSpecial も重い処理ですので、Valueプロパティでの代入に変更すると改善する可能性があります。

Office TANAKA - VBA高速化テクニック[値貼り付けは遅い]

vba

1 ws2.Range("A" & i, "BA" & i).Copy 2 ws1.Range("A" & j).PasteSpecial Paste:=xlPasteValues

上記の部分を下記に修正してみてください。

vba

1 with ws2.Range("A" & i, "BA" & i) 2 ws1.Range("A" & j).Resize(1, .Columns.Count).Value = .Value 3 End with

他のPasteSpecialも同様に修正してみてください。

投稿2020/12/11 08:54

編集2020/12/11 09:03
hatena19

総合スコア33620

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

koyamashinji

2020/12/11 09:38

ご回答誠に有難うございます。 関数が設定されているセルはないので、Copy&PasteをValueプロパティでの代入に変更すると劇的に高速化しました。 こんなにも違うのですね。大変驚きました。 ただ、 with ws2.Range("A" & i, "BA" & i) ws1.Range("A" & j).Resize(1, .Columns.Count).Value = .Value End with にすると、「rangeメソッドは失敗しました worksheetオブジェクト」のエラーが出たので(なぜかは分からない・・) Resizeメソッドに何かしら問題があるのだと勝手に推測し、以下のとおり貼付け先の範囲を指定したところ、問題なく動きました。 with ws2.Range("A" & i, "BA" & i) ws1.Range("A" & j, "BA" & j).Value = .Value End with にすると、「rangeメソッドは失敗しました worksheetオブジェクト」のエラーが出たので(なぜかは分からない・・) Resizeメソッドに何かしら問題があるのだと勝手に推測し、以下のとおり貼付け先の範囲を指定したところ、問題なく動きました。 with ws2.Range("A" & i, "BA" & i) ws1.Range("A" & j, "BA" & j).Value = .Value End with
guest

0

1セルずつとるのではなく2次元配列でまとめてとりましょう!

参考:http://officetanaka.net/excel/vba/speed/s11.htm

投稿2020/12/11 08:46

編集2020/12/11 08:48
rysh

総合スコア874

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

koyamashinji

2020/12/11 11:39

ご回答頂き有難うございます。2次元配列でまとめる方法もあるのですね・・参考になりました。
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問