エクセルVBAのEntireRow.Hiddenをスピードアップしたいのですが・・・。

質問

エクセル９７です。
９行目から７３２行までの大きな表があります。一定の条件のとき、U列がaaaでなくV列もbbbではない行を非表示とするために下記のマクロを作ってみました。
一応思ったとおりには動くのですが行数が多いため、開始してから終了するまで５分以上かかってしまいます。このマクロのように９行目から７３２行まで順にやっていくのではなく、一度に非表示にするような方法はないでしょうか？

Sub TEST()
Sheets("TEST").Activate
For i = 9 To 732
If Cells(i, "U") <> "aaa" And Cells(i, "V") <> "bbb" Then
Cells(i, "V").EntireRow.Hidden = True
Else
Cells(i, "V").EntireRow.Hidden = False
End If
Next i
End Sub

よろしくお願いします。

回答No.4

papayuka · ベストアンサー

#2です。

> tmp = "=IF(OR(U9=""eee"",V9=""fff""),1,"""")"
> としてもダメでした。これはOR条件は使えないのでしょうか？

動くハズですよ、セルに入れる時は　=IF(OR(U9="eee",V9="fff"),1,"")　というただの条件式ですから。
VBAで文字列に " を使うときは "" としてやらねばならないためちょっと変に見えるだけです。セルにこの関数を入れて 1 が立てば、例のマクロで非表示対象になります。

思い通りにならないとしたら条件を勘違いされているのではないでしょうか？

仕組みの説明
1.テンポラリィ範囲を取あえず表示（条件を変えて実行した場合に必要？）
2.テンポラリィ範囲に関数を一気に代入
3.テンポラリィ範囲の数値セル（行）を非表示
4.テンポラリィ範囲の関数をクリア

3 はExcelの操作でやると、テンポラリィ範囲を選択し、編集－ジャンプ－セル選択の選択オプションで「数式」の「数値」のみにチェックを入れた場合に選択される範囲です。
条件式を入れる列を追加出来るならマクロでやらなくてもオートフィルタで出来る内容ですね。

お礼

大変くわしくお教えいただきありがとうございました。
OR条件でも出来ました。
ほんとうに助かりました。

回答No.3

imogasi

Sub test02()
For i = 1 To 732
If i Mod 10 = 1 Or i Mod 10 = 6 Then
Cells(i, "U") = ""
Cells(i, "V") = ""
End If
Next i
End Sub
を実行し、A1:T732に（同じ）データを入れ
Cells(i, "V").EntireRow.Hidden = False を省き
●(注！）不要のようです。
Sub TEST()
Sheets("sheet1").Activate
For i = 9 To 732
If Cells(i, "U") <> "aaa" And Cells(i, "V") <> "bbb" Then
Cells(i, "V").EntireRow.Hidden = True
End If
Next i
End Sub
を実行してみたところ、２秒ぐらいで終了しました。
Application.ScreenUpdating = TRUE,False も入れていません.
入れると一瞬にして終わります。当方９８ｓｅでエクセル２０００で数年前のCPUです。
ですからなにか別の要因で遅くなるのだと思います。
Cells(i, "V").EntireRow.Hidden = Trueを
Rows(i).EntireRow.Hidden = Trueにしてみたりしましたが変りありませんでした。

お礼

ありがとうございました。
Cells(i, "V").EntireRow.Hidden = False を省きましたらかなり早くなりましたが、数秒というわけにはいきませんでした。

回答No.2

papayuka

5分は掛かりすぎですね。
こちらの環境ではそのままのコードで実行しても、3～4秒でした。

#1さんの回答にある Application.ScreenUpdating = False でだいぶ改善されると思いますが、、、

例は空いていそうなセルに判定用関数を入れて一気に非表示にしてます。
複雑な計算式を多用している場合はかえって遅くなるかもしれません。

Sub TEST1()
Dim tmp As Range
　Sheets("TEST").Activate
　Set tmp = Range("IV9:IV732")
　tmp.EntireRow.Hidden = False
　tmp = "=IF(AND(U9<>""aaa"",V9<>""bbb""),1,"""")"
　On Error Resume Next
　tmp.SpecialCells(xlCellTypeFormulas, 1).EntireRow.Hidden = True
　tmp.Clear
End Sub

お礼

ありがとうございました！
一瞬でできました！！
ただ、理屈がよくわからないのです。
U9<>"aaa"かつV9<>"bbb"のとき、そのIV列に1をいれてるのはわかりましたが・・・・。
理屈がよくわからないので別な表のOR条件に応用できませんでした。
ANDをORにかえてもだめなんですね？

補足

本来は別の質問としなければいけないのかも知れませんが・・・・。

上記でU列が"eee"、またはV列が"fff"のときのみ、その行を非表示とする場合、

tmp = "=IF(OR(U9=""eee"",V9=""fff""),1,"""")"

としてもダメでした。これはOR条件は使えないのでしょうか？

回答No.1

popesyu

行単位で表示非表示の判別をせざるを得ない以上、一度にする方法はないでしょうが、まぁアルゴリズムを見直すなら。
最初にソートさせて、U列がaaa、V列もbbbの行をまとめてしまい、それ以外を非表示させるというのが早そうかなと思います。
まず行番号を振っていつでも元に戻せるようにしておく必要がありますが。
でそれ以外の最適化のコツとしては
・メモリリソースを節約
各変数は必ず最適な型で宣言しましょう。
dim i as Long
・描画の省略
その手の作業をする場合は真っ先に
Application.ScreenUpdating = False
と画面表示を更新させないようにします。最後に
Application.ScreenUpdating = true
としておくのを忘れないように。
・分岐の最適化
IF文よりはSELECT文の方が多少効率が良いので、判定はSELECT文でネストさせたらどうかなと。
・With...End With 構文による高速化
同じオブジェクトの処理が続く場合
With Cells(i, "V")
End With
と包みましょう。

お礼

ありがとうございました。