以前、質問で複数の行をRangeに格納し一括で削除する方法を教えていただきました。
実践したコードが以下の通りです。
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
'Unionで指定の行を複数格納
For i = TergetSetSheets.Range("Y" & Rows.Count).End(xlUp).Row To 7 Step -7
If SetRan Is Nothing Then
Set SetRan = TergetSetSheets.Rows(i - p)
Else
Set SetRan = Union(SetRan, TergetSetSheets.Rows(i - p))
End If
Next
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
500行くらいなら0.03秒くらいで処理できていたのですが、
4700行で6.8秒、9400行で52秒になりました。
※描写は停止にしています。
これ以上早く処理を行うことはできるのでしょうか?
なるべくなら行の削除を行いたいと思っています。
なぜなら表の集計をこの後に行うのにあらかじめ不要な行を先に削除しておくことにより
処理速度が上がるのではないかと思っているからです。
いい方法がありましたら知恵を貸してください。
どうかよろしくお願いいたします。
No.2ベストアンサー
- 回答日時:
まず現行マクロの改良としては、「オブジェクトに触る回数を少なくする」
TergetSetSheetsこれはワークシートだと思いますが、ループ内で9000行処理するなら9000回同じシート名を指定することになり、低速化の原因になります。下記のようにすると指定が省略されて速く、しかも見た目スッキリで可読性が上がります。
With TergetSetSheets
For i = .Range("Y" & Rows.Count).End(xlUp).Row To 7 Step -7
If SetRan Is Nothing Then
Set SetRan = .Rows(i - p)
Else
Set SetRan = Union(SetRan, .Rows(i - p))
End If
Next
End With
SetRanはセルではなく行のようですね。遅くなりませんか?1セルだけであっても、一括削除する際にEntireRowとすれば行になりますよ。この比較は私もしたことがないので、確信持っては言えませんが。
次に、削除せず残す方に規則性があるなら、Autofilterを使う方法があります。マクロでなく通常の手動操作でもフィルタがありますが、あれです。あれで削除する行「以外」が表示されるようにして、全選択→コピー→別シートにペースト これでも速いです。これを考えると、手動でもいいんじゃないの?とか藪蛇な思いがあります(笑)
Union の後で一括deleteもAutofilterもそうですが、エクセルでは一気に選択、一気に処理するのが速いです。乱暴に言うと一行ステートメントで「一括処理」するのが速いのです。その意味ではFor Nextループは一回ずつ順番に9000回処理するため、低速化の一因になります。しかし今回の場合、やらない訳には行きませんので残してます。
老婆心ながら、エクセルVBAの質問なら、カテゴリはVisual Basicまたはエクセルが良いです。
回答者1の30246kikuさんの対策とHigh_Scoreさんの対策を試してみました。
----------------------------------------------------------------
データ数 588 9408 65534
従来 0.046875 50.20313 終わらない
対処1 0.0390625 51.44531 終わらない
対処2 0.05078125 52.30859 終わらない
AutoFilter 0.4765625 0.7578125 8.277344
30246kikuさん 0.2070313 0.90625 35.40234
----------------------------------------------------------------
※対処1:オブジェクトに触れる回数を減らす
※対処2:Rowではなくセル指定
以上のような結果になりました。
今回は実装が簡単で処理が速いオートフィルターを使用してみたいと思います。
コピー後の貼り付けでフィルターで除外されたものはコピーされないとは知りませんでした;(非表示だからそのままコピーされるのかと思っていました;)
あと今後もVBAで質問があるときはカテゴリに気を付けたいと思います。
とても勉強になりました。
ありがとうございます。
No.1
- 回答日時:
> 例:
> A B
> 1 太郎 住所
> 2 電話
> 3 メールアドレス
> 4 誕生日
> 5 年齢
> 6 性別
> 7 既婚
> 8 花子 住所
> 以下続く
この7行が繰り返されていて、
例えば、年齢、既婚 部分の行を一気に消したい・・・
という事で良かったでしょうか
作業列として、今使っている最終列+2の所の列を使います。
その作業列に
=IF(INDEX({0,0,0,0,1,0,1},MOD(ROW()-1,7)+1)>0,1,"")
という計算式を埋め込んで、削除する行を特定します。
{0,0,0,0,1,0,1} このパターンは、
左から、住所、電話・・・に対応していて、削除したい所を > 0 で設定します。
{0,0,0,0,1,0,1}
↓誕生日も削除対象にするのなら、左から4つ目の 0 を変更
{0,0,0,1,1,0,1}
以下の Samp1 でどうなりますか?
Option Explicit
Public Sub Samp1()
Dim iRowH As Long, iCol As Long
Dim i As Long
Const CLIMIT As Long = 5000
With ActiveSheet
With .UsedRange
With .Cells(.Rows.Count, .Columns.Count)
iCol = .Column + 2
iRowH = .Row
End With
End With
With .Cells(1, iCol).Resize(iRowH)
.Formula = _
"=IF(INDEX({0,0,0,0,1,0,1},MOD(ROW()-1,7)+1)>0,1,"""")"
.Value = .Value
On Error Resume Next
For i = (iRowH - 1) \ CLIMIT To 0 Step -1
.Cells(i * CLIMIT + 1).Resize(CLIMIT) _
.SpecialCells(xlCellTypeConstants, xlNumbers) _
.EntireRow.Delete
Next
.ClearContents
End With
End With
End Sub
※ 速くならなかったらごめんなさい
※ ScreenUpdating 等は必要に応じて追加してください
なお、確認用データは以下で作ってました
Public Sub testData()
Dim r As Range
For Each r In Range("A1:Z30000")
r = r.Address(False, False)
Next
Columns.AutoFit
End Sub
回答ありがとうございます。
歴然と速くなりました!!
またとても勉強になりました。
今後VBAでプログラムを行うときは参考にさせていただきます。
ありがとうございました。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
このQ&Aを見た人はこんなQ&Aも見ています
-
海外旅行から帰ってきたら、まず何を食べる?
帰国して1番食べたくなるもの、食べたくなるだろうなと思うもの、皆さんはありますか?
-
フォロワー20万人のアカウントであなたのあるあるを披露してみませんか?
あなたが普段思っている「これまだ誰も言ってなかったけど共感されるだろうな」というあるあるを教えてください
-
映画のエンドロール観る派?観ない派?
映画が終わった後、すぐに席を立って帰る方もちらほら見かけます。皆さんはエンドロールの最後まで観ていきますか?
-
海外旅行から帰ってきたら、まず何を食べる?
帰国して1番食べたくなるもの、食べたくなるだろうなと思うもの、皆さんはありますか?
-
天使と悪魔選手権
悪魔がこんなささやきをしていたら、天使のあなたはなんと言って止めますか?
-
VBA .Value=.Value ?
Excel(エクセル)
-
エクセルVBA Unionはなぜ遅い?
Visual Basic(VBA)
-
Excelでのセル内容の高速消去方法
その他(プログラミング・Web制作)
-
-
4
EXCEL マクロ 列の削除に時間がかかる
Excel(エクセル)
-
5
指定した文字があった場合、その行を削除するマクロが欲しいです
Excel(エクセル)
-
6
EXCEL VBAで全選択範囲の解除
Excel(エクセル)
-
7
エクセルVBAで5行目からオートフィルタモードに設定したいたい
Excel(エクセル)
-
8
特定の文字がある行以外を削除するマクロ
その他(Microsoft Office)
-
9
Excel VBA データ削除の高速化
その他(Microsoft Office)
-
10
VBA 複数の列を高速で削除する方法
Excel(エクセル)
-
11
EXCEL VBA マクロ 実行する度に処理速度がどんどん遅くなる原因が知りたい
Excel(エクセル)
-
12
エクセル:マクロ「Application.CutCopyMode = False」って?
Excel(エクセル)
-
13
【Excel VBA】複数ある特定の文字列を含む行を削除
Excel(エクセル)
-
14
エクセルVBA 配列からセルに「関数式」を一気代入したい
Visual Basic(VBA)
-
15
VBA 数値を文字列として貼付したい
Excel(エクセル)
-
16
【EXCEL】【VBA】空欄は飛ばして処理する方法を教えて下さい。
Excel(エクセル)
-
17
VBA 指定した列にある日時データから時間を削除する方法について
Excel(エクセル)
-
18
B列の最終行までA列をオートフィル
Visual Basic(VBA)
-
19
EXCELマクロを使い、空白行ではさまれた範囲の数字を合計
Excel(エクセル)
-
20
エクセルのラベルの値(文字列)を垂直方向で中央揃えにするには?
Excel(エクセル)
関連するカテゴリからQ&Aを探す
おすすめ情報
- ・漫画をレンタルでお得に読める!
- ・人生のプチ美学を教えてください!!
- ・10秒目をつむったら…
- ・あなたの習慣について教えてください!!
- ・牛、豚、鶏、どれか一つ食べられなくなるとしたら?
- ・【大喜利】【投稿~9/18】 おとぎ話『桃太郎』の知られざるエピソード
- ・街中で見かけて「グッときた人」の思い出
- ・「一気に最後まで読んだ」本、教えて下さい!
- ・幼稚園時代「何組」でしたか?
- ・激凹みから立ち直る方法
- ・1つだけ過去を変えられるとしたら?
- ・【あるあるbot連動企画】あるあるbotに投稿したけど採用されなかったあるある募集
- ・【あるあるbot連動企画】フォロワー20万人のアカウントであなたのあるあるを披露してみませんか?
- ・映画のエンドロール観る派?観ない派?
- ・海外旅行から帰ってきたら、まず何を食べる?
- ・誕生日にもらった意外なもの
- ・天使と悪魔選手権
- ・ちょっと先の未来クイズ第2問
- ・【大喜利】【投稿~9/7】 ロボットの住む世界で流行ってる罰ゲームとは?
- ・推しミネラルウォーターはありますか?
- ・都道府県穴埋めゲーム
- ・この人頭いいなと思ったエピソード
- ・準・究極の選択
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
どう増強すべきか
-
シグナル 6(SIGABRT)とは?
-
擬似言語にて
-
エクセルVBAでロックをかけたい
-
n88 Basic に関して(ループ)
-
vbaのエラー対応(実行時エラー...
-
Word VBA。各マクロの間に待ち...
-
特定の名前のオートシェイプの...
-
ExcelのVBAで、選択したファイ...
-
VBAの進捗状況をリアルタイ...
-
IF文に時間(何時から何時ま...
-
途中で処理を中断させたい (ア...
-
エクセル画面のちらつきなくす...
-
¥マークを含むパスの処理につ...
-
C# Webブラウザコントロールの...
-
マクロで、次のコードへ行く前...
-
エクセル VBAで複数セル選択時...
-
複数個のTextBoxでいずれかの内...
-
シェルスクリプトでファイル内...
-
Functionで戻り値を複数返す方法
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
【C#/Java?】try-catchでcatch...
-
IF文に時間(何時から何時ま...
-
private subモジュールを他のモ...
-
vbaのエラー対応(実行時エラー...
-
シグナル 6(SIGABRT)とは?
-
マクロで、次のコードへ行く前...
-
どう増強すべきか
-
Excel VBA セルの名前があるか...
-
特定の名前のオートシェイプの...
-
どうやってもFor文を抜けてしま...
-
特定のファイルを他のプロセス...
-
ExcelのVBAで、選択したファイ...
-
VB6にてネットワーク上にある共...
-
VBA 複数の行を高速で削除する...
-
Word VBA。各マクロの間に待ち...
-
フォルダのアクセス権確認について
-
エクセル VBAで複数セル選択時...
-
VBA For Each 〜 複数条件について
-
【C#】Page_Loadさせない方法に...
-
StatusStripの表示が更新されな...
おすすめ情報