[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『選択シートの特定列を削除』(るい)
こんにちは。
マクロ初心者です。
こちらのサイトや別サイトを参考に、
選択したシートの特定列を削除した後、列を挿入(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
画面の更新を止める。
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
あれ〜 すみません。
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
大きいファイルを作業するのは一度きりなのですが、これが止まってしまうと
元も子もなく、困っておりました。。
各シートへ分割するマクロはスムーズに動くのですが、列の削除・挿入は
やはり作業が重いのでしょうか。
選択するのは元になるシート以外のシートなので、選択ミスはほぼありません。
(選択しないものは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
あれ〜だめですか。。。
じゃぁ、ループするしかないかもですね。
選択しないで、不要なシートは名前で除外して。
それで、落ちるようなら、
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.