[[20160418141440]]　『指定セルへの値貼付け』（T1604）　ページの最後に飛ぶ

『指定セルへの値貼付け』（T1604）

お世話になります。
内容）
ブック(W)シート(W)の内容を以下条件例に従いブック(A)シート(A)に「値貼付け」したいです。
シート(W)は番号を“ひと塊”にして行方向に並んでいます。
番号は最大で「18」までで不定です。
条件例）
・シート(W)のA列をシート(A)のL列とAA列に値貼付け
・シート(W)のE列～I列をシート(A)のN列～R列に値貼付け
・シート(A)の“ひと塊”の先頭行は1,21,41,...と20行あける

 ブック(W)シート(W)
       A    B     C     … E
 1     番号 項目1 項目2 … 項目5
 2     1    ｱ1    ｱ2    … ｱ5
 … 
 12    11   ｵ1    ｵ2    … ｵ5
 13    番号 項目1 項目2 … 項目5
 14    1    ｶ1    ｶ2    … ｶ5
 … 
 31    18   ｺ1    ｺ2    … ｺ5
 32    番号 項目1 項目2 … 項目5
 33    1    ｻ1    ｻ2    … ｻ5
 … 
 …    16   ｿ1    ｿ2    … ｿ5
 ブック(A)シート(A)
       L    M     N     … AA
 1     番号 項目1 項目5 … 番号
 2     1    ｱ1    ｱ5    … 1
 … 
 …    11   ｵ1    ｵ5    … 11
 …
 …
 …
 21    番号 項目1 項目5 … 番号
 22    1    ｶ1    ｶ5    … 1
 … 
 39    18   ｺ1    ｺ5    … 18

自作のマクロはシート(W)の“ひと塊”の開始行と終了行をi,j、シート(A)の開始行をkとして
Do～Loopで“ひと塊”毎に貼付け処理をしており、目で確認できるほど処理が遅いです。
貼付けを「一括処理」するにはどうしたらいいでしょうか？

よろしくお願いします。

＜使用 Excel：Excel2010、使用 OS：Windows7 ＞

 手抜きです。ブロックは、かならず１８行以内（タイトルあわせて１９行以内）としています。
 とりあえず、セルに直接転記しています。遅ければ手を入れます。

 ★列の位置、質問文とサンプルが食い違っていますが質問文にあわせました。

 16:16 使わなくなったコードをそのままにしてあったので消去。

 Sub Test()
    Dim shW As Worksheet
    Dim shA As Worksheet
    Dim tR As Range
    Dim dR As Range
    Dim x As Long
    Dim a As Range

    Application.ScreenUpdating = False

    Set shW = Workbooks("W.xlsx").Sheets("W")
    Set shA = Workbooks("A.xlsx").Sheets("A")
    shA.UsedRange.ClearContents

    With shW.Range("A1", shW.Range("A" & Rows.Count).End(xlUp))
        .Replace What:="番号", LookAt:=xlWhole, Replacement:=Empty
        Set tR = .SpecialCells(xlCellTypeBlanks)
        Set dR = .SpecialCells(xlCellTypeConstants)
        tR.Value = "番号"
    End With

    x = 1

    For Each a In dR.Areas
        a.Columns(1).Offset(-1).Resize(a.Rows.Count + 1).Copy shA.Cells(x, "L")
        a.Offset(-1, 1).Resize(a.Rows.Count + 1, 5).Copy shA.Cells(x, "N")
        x = x + 20
    Next

    shA.Columns("L").Copy shA.Range("AA1")
    Application.Goto shA.Range("A1")

 End Sub

（β） 2016/04/18(月) 15:22

βさん、ありがとうございます。　うまくいきました！
質問の誤記すみませんでした。

ReplaceやtR,dRなど、未だコードの理解には至っていません。

取り急ぎお礼とさせていただきます。
これからもよろしくお願いします。
（T1604） 2016/04/18(月) 18:30

お世話になります。
業務の合間にネット検索等で理解に努めてますが、“白旗”です．．（涙）
すみませんが、以下ご教授ください。

先ず確認ですが、提示いただいたコードは以下の理解でよろしいでしょうか？
・「Replace」でA列にある“番号”を“空欄？”に置換することにより、“番号”を含まないコピー対象セル（dR）を得る
・「dR」は“飛び地”の“ひと塊”となる？
・「Areas」により“ひと塊”ずつのセル範囲を得て、「For Each～」で順にシート(A)に貼付ける

また、高速化に貢献しているのは、
・「Application.ScreenUpdating = False」で画面表示を「OFF」している
・Do-Loop ではなく、For Each-Next を使用している
・Range(Cells(i, 1), Cells(j, 1)).Copy （自作コードで使用）ではなく、「Areas」を使用している
このような理解でよろしいでしょうか？

他にもあるかと思いますが、最も高速化に寄与しているのは何でしょうか？

SpecialCells(...) や Areas の使い方も参考になりましたし、しっかりと
理解しておきたいと思います。

よろしくお願いします。
（T1604） 2016/04/20(水) 09:55

元のソースコードを見せて頂かないと比べられないのですが、おそらくはシートに対する書き込み回数かと思いますよ。
βさんは範囲コピーしています（Resizeに注目）。元のコードでは、1セルずつコピーしていませんでしたか？
（???） 2016/04/20(水) 10:17

 ●先ず確認ですが、提示いただいたコードは以下の理解でよろしいでしょうか？

  ・「Replace」でA列にある“番号”を“空欄？”に置換することにより、“番号”を含まないコピー対象セル（dR）を得る

 　　はい。A列で、"番号" となっているセル（ブロックの先頭行のセル）を取得するため、いったん空白にしています。
 　　こうしておいて、Set tR = .SpecialCells(xlCellTypeBlanks)　とすることで、tR がブロックの最初の各セルの集合体になります。
 　　なお、同様に Set dR = .SpecialCells(xlCellTypeConstants)　これで、A列の　値がある領域を取得しています。
 　　なかみは、A2:A12,A14:A31,・・・・　となっています。

 　　なお、SpecialCellsメソッドは、一般機能で、条件を選択してジャンプ(S) の機能です。

 　　で、tR,dRを取得した後、tR.Value = "番号"　で、tR領域に "番号" を書き戻しています。

  ・「dR」は“飛び地”の“ひと塊”となる？

 　　前述の通り、とびとびのひと塊の集合体です。

  ・「Areas」により“ひと塊”ずつのセル範囲を得て、「For Each～」で順にシート(A)に貼付ける

 　　はい。実際に貼り付けている領域は a.Columns(1).Offset(-1).Resize(a.Rows.Count + 1)
 　　dR 内の 各Areaの1行上（番号行）も含めた領域です。

 ●また、高速化に貢献しているのは、 
 ・「Application.ScreenUpdating = False」で画面表示を「OFF」している 
 ・Do-Loop ではなく、For Each-Next を使用している 
 ・Range(Cells(i, 1), Cells(j, 1)).Copy （自作コードで使用）ではなく、「Areas」を使用している

 　Application.ScreenUpdating = False は、確かにそうですね。
 　xl2003 までは、極端にいえば、実行時間が　何もしないときに比べて半減しました。
 　いまは、そうでもありませんが、かなりの効果はあります。

 　Do/Loop と For/Netx は、そんなに変わりませんね。

 　なにより、貢献しているのは、セルへの書き込み回数です。
 　エクセル上の処理で最も重いものの1つがセルへの書き込み。
 　2007以降、セルの属性が大幅に増えたこともあって、かなり重い処理になります。

 　【書き込み回数】と表現しました。たとえば 1セルずつ100回書きこむのと 100セル一度に書きこむのとでは
 　極端にいえば処理負荷は 1/100 になります。（実際には　書きこみ領域が大きければそれなりに少しかかりますが）
 　今回のコードでは1行ずつ書きこまず、1ブロック（Area) ずつの書き込みにしています。

（β） 2016/04/20(水) 10:45

???さん、βさん

ご丁寧な説明ありがとうございます、書き込み「回数」がキモなんですね。
確かに自作コードをよく見てみると1行ずつ書き込んでいました _ _ ;

いい勉強になりました．．改めてお礼申し上げます。

これからもよろしくお願いします。
（T1604） 2016/04/20(水) 13:33

コメント返信：

[ 一覧(最新更新順) ]

[[20160418141440]] 『指定セルへの値貼付け』（T1604） ページの最後に飛ぶ

[[20160418141440]]　『指定セルへの値貼付け』（T1604）　ページの最後に飛ぶ