Excel (VBA)

Excel VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(指定なし : 指定なし)
For~Nextの処理の遅さについて
投稿日時: 18/08/09 15:50:49
投稿者: つま

皆様いつもお世話になっております。
初歩的?な繰り返し処理の質問をさせていただきます。
 
F3 "品番-カラー"
G3 空白
H3 空白
I3 サイズ
J3 空白
K3 空白
L3 空白
 
となっているデータに関して、
 
G3 F3の"品番"部分を入力
H3 F3の"カラー"部分を入力
K3 H3をコピー
L3 I3をコピー
 
という処理を500行ほど行う必要がありまして、
Google先生に頼りながら以下のようなFor~Next文を書きました。
---------------------------------------------------
'==コード&カラー分離
  Dim code As String
    Dim f As Long
    Dim FF As Long
 
    FF = Cells(Rows.Count, "F").End(xlUp).Row
    For f = 3 To FF
     code = Cells(f, "F")
     Cells(f, "G") = Left(code,6)
     Cells(f, "H") = Right(code,3)
    Next f
 
'==カラーコピー
    Dim h As Long
    Dim HH As Long
 
    HH = Cells(Rows.Count, "H").End(xlUp).Row
    For h = 3 To HH
     Cells(h,"H").Copy Cells(h,"K")
    Next h
 
'==サイズコピー
    Dim i As Long
    Dim II As Long
 
    II = Cells(Rows.Count, "I").End(xlUp).Row
    For i = 3 To II
     Cells(i,"I").Copy Cells(i,"L")
    Next i
---------------------------------------------------
この記述で希望している動作はしました。
しかし、それほど悪いスペックのPCではないのにも関わらず、
他の箇所の記述に比べて処理がかなり遅く感じます。
(1秒に5行程度の処理×500行×3つのFor~Nextなので、約6分程度)
 
この現象について、
@ For~Nextを使いつつ、もっと処理の早い書き方があるよ
A 別の繰り返し処理を使うと早くなるよ
B 諦めて6分待ちなさい
 
上記のようなアドバイスをいただけると助かります。

回答
投稿日時: 18/08/09 16:35:58
投稿者: sk

引用:
Cells(f, "G") = Left(code,6)

引用:
Cells(h,"H").Copy Cells(h,"K")

「値/数式の代入」と「セルのコピー/貼り付け(値/数式/書式)」とは
異なる操作なのですが、両者の違いを明確に理解された上で
Copy メソッドを使用されているのでしょうか。
 
引用:
(1秒に5行程度の処理×500行×3つのFor~Nextなので、約6分程度)

少なくとも 3 つの For 文に分ける必要があるようには
思えません。

回答
投稿日時: 18/08/09 16:51:31
投稿者: Suzu

For 3〜Max行
   G3 F3の"品番"部分を入力
   H3 F3の"カラー"部分を入力
   K3 H3をコピー
   L3 I3をコピー
Next
 
にすれば、500行×1回で良いのでは?
 
その場合、Max行が 列により違うのであれば、UsedRangeでも使いますか。
WorkSheets("Sheet名").UsedRange.Rows(Worksheets("Sheet名").UsedRange.Rows.Count).Row
 
H3 も、K3 も 同じ値が入るのであれば
一気に処理してしまえば?
Excel.Union(Range("H3"), Range("K3")).Value = "値"
 
書式も含めてなら、コピーが必要でしょうけど、
何となく値のみの書き換えの様に見えます。 Copy の必要性があるか考えてみてください。

投稿日時: 18/08/09 17:00:21
投稿者: つま

>>sk様
>>Suzu様
 
ご回答ありがとうございます。
 

引用:
書式も含めてなら、コピーが必要でしょうけど、
何となく値のみの書き換えの様に見えます。 Copy の必要性があるか考えてみてください。

引用:
Cells(f, "G") = Left(code,6)
Cells(h,"H").Copy Cells(h,"K")
「値/数式の代入」と「セルのコピー/貼り付け(値/数式/書式)」とは
異なる操作なのですが、両者の違いを明確に理解された上で
Copy メソッドを使用されているのでしょうか。

全く考えておりませんでした。。
質問文に記載していない箇所で既に書式の設定をしているので、
Copyメソッドを使う必要は全く無いですね。=を使用してみます。
 
引用:
For 3〜Max行
   G3 F3の"品番"部分を入力
   H3 F3の"カラー"部分を入力
   K3 H3をコピー
   L3 I3をコピー
Next
  
にすれば、500行×1回で良いのでは?

引用:
(1秒に5行程度の処理×500行×3つのFor~Nextなので、約6分程度)
少なくとも 3 つの For 文に分ける必要があるようには
思えません。

仰る通り、最終行が同じなのでシンプルに一度のFor~Nextで良いですね…。
 
初心者だからと言い訳したくなりますが、
こんな初歩的なミスにも気づかない自分にがっかりです。。
精進いたします。
 
この度はご回答ありがとうございました。
18時頃クローズさせていただきます。

回答
投稿日時: 18/08/09 17:15:26
投稿者: sk

引用:
質問文に記載していない箇所で既に書式の設定をしているので、
Copyメソッドを使う必要は全く無いですね。

ならば値だけを貼り付ければよいでしょう。
 
------------------------------------------------------------------
Sub subTest()
 
    Dim xlsWorksheet As Excel.Worksheet
    Dim xlsSourceRange As Excel.Range
    Dim xlsDestinationRange As Excel.Range
     
    Dim lngRow As Long
    Dim lngFirstRow As Long
    Dim lngLastRow As Long
    Dim strCode As String
     
    Set xlsWorksheet = ActiveSheet
     
    With xlsWorksheet
        lngFirstRow = 3
        lngLastRow = .Cells(.Rows.Count, 6).End(xlUp).Row
        If lngFirstRow > lngLastRow Then
            Set xlsWorksheet = Nothing
            Exit Sub
        End If
         
        'コード&カラー分離
        For lngRow = lngFirstRow To lngLastRow
            strCode = .Cells(lngRow, 6).Value
            .Cells(lngRow, 7).Value = Left(strCode, 6)
            .Cells(lngRow, 8).Value = Right(strCode, 3)
        Next
      
        'カラーのコピー(H列の値をK列に貼り付け)
        Set xlsSourceRange = .Range(.Cells(lngFirstRow, 8), _
                                    .Cells(lngLastRow, 8))
        Set xlsDestinationRange = .Range(.Cells(lngFirstRow, 11), _
                                         .Cells(lngLastRow, 11))
        xlsSourceRange.Copy
        xlsDestinationRange.PasteSpecial xlPasteValues
         
        'サイズのコピー(I列の値をL列に貼り付け)
        Set xlsSourceRange = .Range(.Cells(lngFirstRow, 9), _
                                    .Cells(lngLastRow, 9))
        Set xlsDestinationRange = .Range(.Cells(lngFirstRow, 12), _
                                         .Cells(lngLastRow, 12))
        xlsSourceRange.Copy
        xlsDestinationRange.PasteSpecial xlPasteValues
        Application.CutCopyMode = False
 
        Set xlsDestinationRange = Nothing
        Set xlsSourceRange = Nothing
        .Cells(lngFirstRow, 6).Select
         
    End With
         
    Set xlsWorksheet = Nothing
 
End Sub
------------------------------------------------------------------

回答
投稿日時: 18/08/09 17:27:21
投稿者: sk

補足:

引用:
xlsSourceRange.Copy
xlsDestinationRange.PasteSpecial xlPasteValues

この部分に関しては更に高速化する手法もありますが、
とりあえずは「各セルへの値の代入/コピー貼り付けを 1 つずつ実行する」
のではなく、「範囲ごとコピー/貼り付けする(ループしなくてもよい)」
という方法があることをご理解いただければよいかと思います。

回答
投稿日時: 18/08/09 17:28:38
投稿者: TAKA君

それにしても6分は遅いですね、、
 
画面の左下で[再計算]がちらちらしたりしていませんか?
数式が多く入ったブックを開いた状態で
何度も書き込むようなマクロを走らせると時間がかかります。
 
もし[再計算]がちらちらするようなら
 

  Application.Calculation = xlCalculationManual '計算方法を手動にする
    
    '処理内容
    
  Application.Calculation = xlCalculationAutomatic '計算方法を自動に戻す

 
このようにするだけでぐっとはやくなります。

回答
投稿日時: 18/08/09 17:42:40
投稿者: TAKA君

こんな感じですね。
 

Sub Test1()
    Application.Calculation = xlCalculationManual
    Dim I As Long
    With ThisWorkbook.Sheets("Sheet1")
        For I = 3 To .Cells(.Rows.Count, "F").End(xlUp).Row
            .Cells(I, "G") = Left(.Cells(I, "F"), 6)
            .Cells(I, "H") = Right(.Cells(I, "F"), 3)
            .Cells(I, "K") = .Cells(I, "H").Value
            .Cells(I, "L") = .Cells(I, "I").Value
        Next I
    End With
    Application.Calculation = xlCalculationAutomatic
End Sub

回答
投稿日時: 18/08/09 18:28:18
投稿者: Suzu

K と L の処理はループの外で良いのね ^^;
 
sk さんのを見るまで気づかないか俺。。。
 
となると。。。
G3 : =Left(F3,6)
H3 : =Right(F3,3)
K3 : =H3
L3 : =I3
のオートフィルでも。。
 
再計算がどんな頻度で必要になるのかで、
・セルへの数式
・VBA
とするかを決めれば良いでしょう。

回答
投稿日時: 18/08/09 18:33:49
投稿者: WinArrow
投稿者のウェブサイトに移動

すでに的確な回答がついているので、
遅い原因をつぶすヒント
 
決して、For〜Nextが起因しているわけではない。
 
シートを取扱う場合
(1)数式(特に、揮発性関数)があると、再計算に時間がかかる。
 再計算を抑制する
(2)セルに代入するときにちらつきを抑制する
(3)クリップボードを使わない方法を考える。
(4)セルに代入する値を配列変数に格納し、一挙にセルに代入する。
(5)Excelが用意している関数をセルに代入し、後で「値」返還する
 
など
 
 
 

投稿日時: 18/08/09 19:31:08
投稿者: つま

>>皆様
 
ご教授ありがとうございます!
個別にお礼が出来ず申し訳ありませんが、皆様の回答を教本にさせていただきます。