[[20170223161200]] 『選択シートの特定列を削除』(るい) ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『選択シートの特定列を削除』(るい)

こんにちは。
マクロ初心者です。
こちらのサイトや別サイトを参考に、
選択したシートの特定列を削除した後、列を挿入(1列)する
というマクロを組んでみました。

−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
Sub ?A列消去と挿入()

'シートを選択して行うこと!

Call 選択シートの削除
Call 選択シートに列を追加

End Sub

Sub 選択シートの削除()

  With ActiveWindow
    For Each ws In .SelectedSheets
      If TypeName(ws) = "Worksheet" Then
        ws.Activate
ws.Range("A:N").Delete
End If
    Next ws
  End With

End Sub

Sub 選択シートに列を追加()

  With ActiveWindow
    For Each ws In .SelectedSheets
      If TypeName(ws) = "Worksheet" Then
        ws.Activate
ws.Range("A:A").Insert  '列を入れる範囲を指定(A:B,C;Dなど)
End If
    Next ws
  End With

End Sub
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
これを115シート分行うのですが、とても列が長いシートのせいか
(一番端の列がAEMまであります)
すごく時間がかかってしまいま、完了する前に止まってしまいます。
どこか直せばさくっと動くようになりますでしょうか。
それとも115シートのAEM列まであるようなファイルはマクロを使用しない方がいいのでしょうか・・・。
何かいい案があればご教示いただきたく、よろしくお願い致します。

< 使用 Excel:Excel2010、使用 OS:Windows7 >


ども^^
これだとどうなりますか?

Sub 任意の列消去と挿入()

    '選択されたシートが対象になります。
    Application.Range("A:N").Delete
    Application.Range("A:A").Insert
End Sub

>それとも115シートのAEM列まであるようなファイルはマクロを使用しない方がいいのでしょうか・・・。
どうなんでしょう。
115個もシートがあるブックを扱ったことが無いのでなんとも・・・
まぁ、マクロ化するなら、
1個づつファイルにしても不都合はないかと。。。

(まっつわん) 2017/02/23(木) 16:53


あ、グラフシートを選択してたら、当然エラーになります。
(まっつわん) 2017/02/23(木) 16:54

こんにちは

画面の更新を止める。

Application.ScreenUpdating = False
'処理
Application.ScreenUpdating = True

計算を手動にして、最後に自動に戻す。

とかを試してみてはどうですか?

(ウッシ) 2017/02/23(木) 16:54


まっつわん様

ありがとうございます。
シートを複数選択(作業グループ)にして実行しても、
1つのシートしか削除されませんでした。
何かやり方が違いますでしょうか?
(るい) 2017/02/23(木) 17:08


 すごいですね。

 ためしに A〜AEM まで、20行程度のシートを115枚作ってみましたら 9メガを超えました。
 これに対してマクロ処理を行うのが妥当かどうかということ以前に、こういった膨大なデータをエクセルで持つこと自体が妥当なのかどうかを
 検討されたほうがよろしいかも。

 ところで、この処理は1回こっきりの臨時処理ですか?
 それとも、定期的に、このようなデータが、どこかから送られてきて、それを、そのたびに列削除、列挿入をするという業務なんでしょうか?
 もし、定期的な処理だとすれば、そのデータの送り先に、るい さんが望む形で送ってもらうように依頼はできないのでしょうかね?

 それと、115枚の中の何枚を処理するのかわかりませんが、SelectedSheet を相手にしているということは 100枚処理したければ
 100枚のシートを、間違いなく選択しなければいけませんよね?
 慣れれば簡単に間違いなく選択できるものでしょうか?(私がやってみたところ、何度も選び間違いをしてしまいました)

 すべてのシートを相手にしてはけないのでしょうか?
 あるいは、これとこれは相手にしないという決め打ちはできないものでしょうか?

 まぁ、こういったことが仕様だといわれれば、御意! ですけど。

 処理速度の改善になるかもしれないポイントを列挙してみます。

 1.処理の最初に Application.ScreenUpdating = False をいれておけば、気持ち、処理時間が短くなるでしょう。
 2.シートに数式がかなりちりばめられているとすれば、
   処理の最初に Application.Calculation = xlCalculationManual、処理の最後に Application.Calculation = xlCalculationAutomatic を記述しておけば
   これも、効果があるかもしれません。
 3.ws.Activate は、この処理をする上で不要なコードですし、シートをアクティブにするという無駄な動作になりますので、消しましょう。
   ほんの少し、処理時間が短くなるでしょう。
 4.今、2つのプロシジャを実行しているわけで、それぞれのプロシジャで、選択された各シートをループで処理しているわけですが
   各シート毎に列削除した後、続けて列挿入する。
   このコードを、A列消去と挿入 の中にひとまとめにして書けば これまた 少しは改善するかも。

( β) 2017/02/23(木) 17:10


>1つのシートしか削除されませんでした。

あれ〜 すみません。

5つぐらいのシートで
3つぐらい選択して実行したらこちらではちゃんとできたつもりでしたが。。。。

も一回試してみます・・・

(まっつわん) 2017/02/23(木) 17:14


ウッシ様
ありがとうございます。
少し早くなった気がしました。

ちなみに下記のように
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
Sub ?A列消去と挿入()

Application.ScreenUpdating = False

'シートを選択して行うこと!

Call 選択シートの削除
Call 選択シートに列を追加

Application.ScreenUpdating = True

End Sub
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−

親マクロにのみいれたのですが、この場合子マクロ
(呼び出している選択シートの削除・列を追加のマクロ)にも入れた方がいいのでしょうか?
初心者の質問で申し訳ありません。。

(るい) 2017/02/23(木) 17:14


失礼。余分なものを書き過ぎたみたいです。

Sub 任意の列消去と挿入()

    '選択されたシートが対象になります。
    Range("A:D").Delete
    Range("A:A").Insert
End Sub

(まっつわん) 2017/02/23(木) 17:23


β様
ありがとうございます。
こちらは毎月行う作業ではなく、一回のみの作業となります。
作業としては、一つのシートにまとまっているデータを、行数毎に複数シートに分割し
(マクロで行います)、その後上記の作業を行い、最後に各シートを各ブックへと保存します。ただこのまとまっているシートがまた3万行を超えるシート(列はAEM列まで)のため、ファイルはものすごく大きくなってしまっています。

大きいファイルを作業するのは一度きりなのですが、これが止まってしまうと
元も子もなく、困っておりました。。
各シートへ分割するマクロはスムーズに動くのですが、列の削除・挿入は
やはり作業が重いのでしょうか。

選択するのは元になるシート以外のシートなので、選択ミスはほぼありません。
(選択しないものは1つだけ)

改善策を提案いただきありがとうございます!
数式は入っていないシートなので、ws.Activateを消してやってみようと思います。

(るい) 2017/02/23(木) 17:25


 皆さんのいわれていることをまとめると以下です。
 これ以外のサブプロシジャは不要です。

 Sub 列消去と挿入()      'シートを選択して行うこと!
    Dim ws As Worksheet

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    For Each ws In ActiveWindow.SelectedSheets
        If TypeName(ws) = "Worksheet" Then
            ws.Columns("A:N").Delete
            ws.Columns("A").Insert
        End If
    Next

    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic

 End Sub

( β) 2017/02/23(木) 17:31


 >(選択しないものは1つだけ) 

 であれば、逆に、対象外のシートを選択して処理したほうがスムーズですよ。
 さらに、そのシートのシート名が決まっているわけですから、マクロ内でそれを除外すれば
 選択してから実行ということも不要になりますね。

( β) 2017/02/23(木) 17:34


まっつわん様

何度もありがとうございます。
やってみましたが、やはり1つのシートしか削除されません。。

ちなみに新しいモジュールにまっつわん様の式のみを入れて実行してしまっているのですが
もしかして私の式のどこかを修正するのでしょうか??

(るい) 2017/02/23(木) 17:34


β様
アドバイスありがとうございます。

>対象外のシートを選択して処理したほうがスムーズですよ。
私もそう思い試してみたのですが、うまくいかずにあきらめました。。
自分で組めるマクロに限界があり・・・もっと勉強しますね。

(るい) 2017/02/23(木) 17:37


 >ちなみに新しいモジュールにまっつわん様の式のみを入れて実行してしまっているのですが 
 >もしかして私の式のどこかを修正するのでしょうか?? 

 そうですよ。まっつわんさんのアドバイスは、るいさんのコードの一部分を変更しましょうということですから。
 私が( β) 2017/02/23(木) 17:31でアップしたコードを参照願います。

 ところで、対象外のシートがきまっているなら、シートを選択する必要はなく、

 Sub 列消去と挿入()
    Dim ws As Worksheet

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    For Each ws In Worksheets
        If ws.Name <> "対象外シート名" Then '★
            ws.Columns("A:N").Delete
            ws.Columns("A").Insert
        End If
    Next

    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic

 End Sub

 こうできますね。★のところは実際に対象外シート名にしてください。

( β) 2017/02/23(木) 17:40


>やってみましたが、やはり1つのシートしか削除されません。。
新しい標準モジュールにコードをコピペ(式とは言わないかな^^;)で、
試してみたんですが。。。。。

あれ〜だめですか。。。
じゃぁ、ループするしかないかもですね。

選択しないで、不要なシートは名前で除外して。

それで、落ちるようなら、
20シートに1回ぐらい休憩を入れると、
動作が安定すると思います。

Sleepで検索するとサンプルあると思います。

(まっつわん) 2017/02/23(木) 17:44


 >20シートに1回ぐらい休憩を入れると、 
 >動作が安定すると思います。

 あぁ、そうですね。挿入や削除といった同じ処理をループ内で繰り返すと、エクセルが落ちてしまうというのは
 エクセルの「得意技」ですから。

 API の Sleep じゃなくても DoEvents で充分な気がします。
 かつ、たかだか 115シートですから、シート毎に DoEvents を入れればいいと思いますね。

 私がアップしたコードでいえば

    For Each ws In Worksheets
        If ws.Name <> "対象外シート名" Then '★
            ws.Columns("A:N").Delete
            ws.Columns("A").Insert
        End If

        DoEvents

    Next

 こんな感じで。

( β) 2017/02/23(木) 18:08


β様

申し訳ありません、 2017/02/23(木) 17:31 に投稿いただいた内容を
見過ごしておりました><ご教示いただきありがとうございます。
2017/02/23(木) 17:40に記載いただいたレコードで回してみました。
なんとなく早くなった気がします!^^
エクセルが落ちることはなく、完了しました。ありがとうございました!
(DoEvents というのも入れてみます。)

まっつわん様
私の理解不足でご迷惑をおかけしましたが、無事に全シート完了することができました。
色々と試していただき、ありがとうございました!

まずは教えて頂いた内容をしっかり理解し、今後もこちらでお勉強させれもらいたいと思います。ありがとうございました!

(るい) 2017/02/23(木) 18:39


ご回答くださった皆様

一応ご報告ですが、やはりファイルが重すぎるようで
途中でメモリ不足になってしまいました。。
※なにせ、70MB以上のファイルになってしまいます。

作業を分割するなど、少し考えてみます。
ありがとうございました。
(るい) 2017/02/23(木) 20:34


コメント返信:

[ 一覧(最新更新順) ]


YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.