[[20201020151733]]　『オートフィルタ後の行削除に時間がかかります』（しのみや）　ページの最後に飛ぶ

『オートフィルタ後の行削除に時間がかかります』（しのみや）

 【元データ】
 A列からAC列にデータが入っており
 全行が、8412行

 AD列に判断列があり、１か空白が入っています
 １が入っている行が、　1475行
 空白行　　　　　が、　6937行

 【やろうとしていること】
 オートフィルタでAD列の空白行を絞り込み、
 その行を選択し、右クリックで行削除をしています

 行削除をすると砂時計になり
 ３分ほど待っていないといけない状況になります

 ３分待たずにESCを押すと行削除されずに
 砂時計が解除され、画面が触れる状況になります

 手作業でもマクロでも時間がかかります

 【環境】
 シンクライアント

 編集 - クリア - 書式のクリアしても変わらず
 新規のエクセルファイルに値貼り付けしても変わらず
 計算方法を手動にしても変わらず
 データ行が多いのと、パソコンの性能、
 シンクライアントなので反応が遅い？のがあるのではと
 思っていますが…

 【回避策】
 ３分待つのは長いので、
 データ - 並べ替え（降順）をし、
 空白行まで選択後、行削除をしています
 こちらであれば、２～３秒ほどで済みます

 回避策の方法でマクロを書いて使おうと思っていますが、
 他のマクロの仕組みを作るときにも必要な作業なので、
 スマートな方法があればお教え頂きたいです

 今回のケースは、判断は○と×のものに１を入れるですが
 ISERRORとVLOOKUPとIFを組み合わせる場合もあります

 【回避策の記述】
    With Sheets("Data")

        Cur最終行 = .Cells(Rows.Count, "A").End(xlUp).Row

        'Y列が○と×の行に１
        .Range("AD1").Value = "[判断]"
        .Range("AD2：AD" & Cur最終行).FormulaR1C1 = "=IF(OR(RC[-5]=""○"",RC[-5]=""×""),1,"""")"
        .Columns("AD:AD").Value = .Columns("AD:AD").Value

        '判定を降順に並べ替え、空白行を行削除
        With .Sort
            .SortFields.Clear
            .SortFields.Add Key:=Range("AD2:AD" & Cur最終行), _
                                    SortOn:=xlSortOnValues, _
                                    Order:=xlDescending, _
                                    DataOption:=xlSortNormal

            .SetRange Range("A1:AD" & Cur最終行)
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .Apply
        End With

        Cur作業最終行 = .Cells(Rows.Count, "AD").End(xlUp).Row
        .Rows(Cur作業最終行 + 1 & ":" & Cur最終行).Delete Shift:=xlUp

        .Columns("AD:AD").Delete Shift:=xlToLeft

        Cur最終行 = .Cells(Rows.Count, "A").End(xlUp).Row

    End With

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

　＞今回のケースは、判断は○と×のものに１を入れるですが

数式で

を返している場合は空白とは言いません。
長さ0の文字列を表示し空白に見せているだけです。
たまにスペースを入れて空白に見せている人もいますが、
これも同様に空白ではなく何らかの文字なので、
お知りおきを。

で、本題。
行や列の削除は遅いので、
並べ替えて良ければ、
クリアしてから並び替えするのが速いようです。（今回の場合は手順が反対でもよさそう？）
あと、数式がシート上にたくさんあるようなら、
再計算を一旦手動に変えてやると処理速度の向上が見込めます。

その他、
「VBA 高速化」で検索してみれば、
高速化のテクニックが紹介されていると思います。

さらなる改善策があるかどうか判別しがたいので
（できるだけ同じ環境にして試してみないと何とも言えない部分もあるかもしれないので）、

○と×以外には何がありますか？
空白セルもありますか？
判断する列には数式がありますか？(AD列の5列左の列？）

（まっつわん） 2020/10/20(火) 18:17

 まっつわんさん ご丁寧にありがとうございます

 ""はisnullという空白ではないけれども何も入っていない状態と記憶しています
 説明で安易に空白としてしまいました…失礼しました

 >クリアしてから並び替えするのが速いようです。（今回の場合は手順が反対でもよさそう？）
 クリアしてから並べ替えとは…どうするのでしょうか
 並べ替えないと、クリアしてよい行の判断ができないと思っておりましたが
 何か方法はあるのでしょうか
 編集 - ジャンプでセル選択をするでしょうか

 >再計算を一旦手動に変えてやる
 数式はセルに残さないように都度値貼り付けをしています
 手動にして試してみましたが、変化がありませんでした

 >○と×以外には何がありますか？
 この質問箱に質問させて頂くために、○と×にしていますが、
 全部で３４項目ある中の２項目だけに１を入れて１が入っている行のみ残そうとしています
 ３４項目の中に空白セルもあります

 もともと、AccessクエリをExcelで再現する必要があって
 https://www.excel.studio-kazu.jp/kw/20200904144824.html
 こちらで、１を入れて絞り込んでいく方法を教えていただいて
 活用させてもらっています

 ですので、他のシートでは
    With Sheets("Data2")

        Cur最終行 = .Cells(Rows.Count, "A").End(xlUp).Row

        'コードが61・62以外のものに１
        .Range("AD1").Value = "[判断]"

        .Range("AD2:AD" & Cur最終行).FormulaR1C1 = "=IF(OR(RC[-6]*1=61,RC[-6]*1=62),"""",1)"
        .Columns("AD:AD").Value = .Columns("AD:AD").Value

 このような状態で、並べ替えをし行削除をしていこうと考えているシートもあります

 >判断する列には数式がありますか？
 都度値貼り付けをしていますので、数式はありませんです

 全体で１８シートあり、この感じですとSTARTしてから終了まで４～５分かかると思っていますが、
 許容範囲です　
 高速化を求めているというより
 >行や列の削除は遅い 
 ことが前提としてあって、
 他のマクロの仕組みを作るとしても、オートフィルタ＆行削除は回避したほうがよいな…と頭に入れておいた
 ほうがよさそうですので
 それをどんな風に回避をされているのかな…と勉強させて頂ければと思っています

 説明が下手で長くなってしまってｽﾐﾏｾﾝ
（しのみや） 2020/10/21(水) 10:05

Sub test()

    Dim t

    t = Timer

    With ActiveSheet.Range("A1").CurrentRegion
        .AutoFilter Field:=.Columns("X").Column, Criteria1:=Array( _
        "20", "21", "22", "23", "24", "25", "26", "27", "28", "29", "30", "31", "32", "33", "34", "35" _
        , "36", "37", "38", "39", "40", "41", "42", "43", "44", "45", "46", "47", "48", "49", "50", _
        "51", "52", "53", "54", "55", "56", "57", "58", "59", "60", "63", "64", "65"), _
        Operator:=xlFilterValues
        .Offset(1).ClearContents
        .AutoFilter
        .Sort key1:=.Range("X1")
    End With

    MsgBox Timer - t & "秒"
End Sub

適当にA列からAD列まで1万行分くらい、
20～60までの数字を入れて、
X列には、20～65までを順に入れてから、
上記のコードで、
X列をみて61と62以外を消してみました。

オートフィルターの部分はマクロの記録を流用してます。

これで0.3秒くらいでできそうですけど、、、、
こちらのマシンは

CPU:i5-7200U 2.5GHz　64ビット
メモリ8G
Win10
エクセル2019

です。
（まっつわん） 2020/10/21(水) 18:45

 まっつわんさん

 実データをコピーして１万行にして
 何度か試してみたところ私の環境で0.41～0.59秒でした

 いただいている記述は、判断列を追加せずにできるもので
 .Offset(1).ClearContentsを使って
表範囲をまとめて削除するを使うと早いのですね

 マクロの記録でｺﾈｺﾈして頭が固くなっている私では 
 とても思いつかない方法でした

 活用させてもらいます　ありがとうございました

（しのみや） 2020/10/22(木) 12:02

 > いただいている記述は、判断列を追加せずにできるもので

数式を入れてやっても目立った差はないかも知れませんが、
高速化を目指すなら、1行でも行数を減らしたいかなぁと思ったまでです。
（あと、数式が解読できなかった＾＾；）
数式を入れてやるとコードがすっきりしそうです。
シートを汚すかコードペインを汚すか、
条件によって臨機応変に対応してください。

 > .Offset(1).ClearContentsを使って
 >表範囲をまとめて削除するを使うと早いのですね

フィルターモード中は、
非表示セルの操作は無視されます。
なのでざっくりオートフィルターのセル範囲を指定しておけば、
ループしたり他の機能で検索し、
条件分岐で処理を分ける必要がないです。
で、「削除」しようとすると遅くなりますので、
「クリアして並び替え」
という操作をします。

 >使って
という表現は僕には違和感があります。
確かに命令語を「使う」のですが、
個人的には、
「1行分セル範囲を下にずらした範囲を、値クリアする。」
とVBA語で書いただけです。

どうしたいか、自分の意思をコードに書くというイメージで、
コードを書いていった方がいいかなと思いました。

（まっつわん） 2020/10/22(木) 18:48

コメント返信：

[ 一覧(最新更新順) ]

[[20201020151733]] 『オートフィルタ後の行削除に時間がかかります』（しのみや） ページの最後に飛ぶ

[[20201020151733]]　『オートフィルタ後の行削除に時間がかかります』（しのみや）　ページの最後に飛ぶ