Excelで指定のキーワードを含む行を削除したいです。

既に http://oshiete.goo.ne.jp/qa/3705773.html のようなサイトでも類似の質問があり、試してみたのですが。
どうもうまくいきません。
Inputbox等は必要ありませんので、シンプルに「G列に指定のキーワードを含む行があれば、その行すべてを削除する」といったマクロがあればお教えいただきたいのですが。
データは30万件ほどあり結構重い作業になるかもですが…。
前回も似たような質問をしたのですが。 http://q.hatena.ne.jp/1416654240
応用させる実力がまだありませんでして…よろしくお願い致します。

回答の条件
  • 1人5回まで
  • 登録:2015/02/24 18:18:20
  • 終了:2015/03/01 20:50:20

ベストアンサー

id:Mook No.4

Mook回答回数1312ベストアンサー獲得回数3912015/02/25 23:30:54

ポイント300pt

まともに行削除をすると数分オーダーなので、削除を一度にする例です。
セルの結合等あるとできないので、そのときは0ポイント配分で結構です。

作業列としてZ列を使用しているので、データがZ列以降にもある場合は、
コード中のZを十分大きな列名に変更してください。

30万行のデータで約3秒でした。

Option Explicit

Sub Main()
    Dim st
    st = Timer()
    DeleteRow "りんご", "G"
    Debug.Print Timer() - st
End Sub


Sub DeleteRow(keyWord As String, searchCol As String)
    Const DelNum = 9999999
    Const WorkCol = "Z"
    Dim lastRow As Long
    lastRow = Cells(Rows.Count, searchCol).End(xlUp).Row
    
    Dim kt
    kt = Cells(1, searchCol).Resize(lastRow)
    
    Dim st
    st = Cells(1, WorkCol).Resize(lastRow)
    
    Dim r As Long
    For r = 1 To lastRow
        If InStr(kt(r, 1), keyWord) = 0 Then
            st(r, 1) = r
        Else
            st(r, 1) = DelNum
        End If
    Next

    Cells(1, WorkCol).Resize(lastRow) = st
    
    ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add _
        Key:=Cells(1, WorkCol).Resize(lastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveSheet.Sort
        .SetRange Range("A1").Resize(lastRow, Cells(1, WorkCol).Column)
        .Header = xlGuess
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    Dim f As Range
    Set f = Columns(WorkCol).Find(DelNum, lookat:=xlWhole)
    If Not f Is Nothing Then Rows(f.Row & ":" & lastRow).Delete
    
    Columns(WorkCol).ClearContents
End Sub
他3件のコメントを見る
id:Mook

黄色くなったときに、r と keyWord、それから LasrRow の値は何でしょうか。
また G列の r 行にはどのようなデータがあったでしょうか。
変数の中身は、マウスカーソルを変数の上に持っていけば確認できます。

2015/02/28 15:16:54
id:moon-fondu

あ、知らなかったです、確かにマウスカーソルを持っていったらr=77643行目がヒットしておりまして、そこに「#NAME?」と記載されてました!
消してもう1回やると、次は132329行目に「#N/A」とありました。
それも消して、もう1回やると・・・うまくいきました!
ありがとうございますヽ(´▽`)/

2015/03/01 20:47:03

その他の回答(3件)

id:a-kuma3 No.1

a-kuma3回答回数4488ベストアンサー獲得回数18572015/02/24 20:55:37

ポイント100pt

こんな感じで、どうでしょうか。

Const SPEED_UP = False          ' True で速くなる

Const MAX_ROW = 300000          ' 突っ走るのが怖いので
Const KEYWORD = "りんご"

Sub Delete_Row_Include_Keyword()

    On Error GoTo ErrorHandler

    If SPEED_UP Then
        'ワークシートに描画しない
        Application.ScreenUpdating = False
    End If

    r = 1
    Do While r < MAX_ROW
        Set c = Cells(r, 7)
        If IsEmpty(c) Or c.Value = "" Then
            Exit Do
        End If

        If InStr(c.Value, KEYWORD) > 0 Then
            c.EntireRow.Delete
        Else
            r = r + 1
        End If
    Loop

FINAL:
    If SPEED_UP Then
        '結果を描画する
        Application.ScreenUpdating = True
    End If
    Exit Sub

ErrorHandler:
    GoTo FINAL


End Sub

行を削除したいシートを選択した状態で、このサブルーチンを実行してください。
先頭の方にある KEYWORD を、適当に変えてください。

G列のセルが、空の行が出てくると、そこで止まります。
処理が延々と終了しないのは、ちょっと恐いので、行数のカウントで最大の行数も指定してます。

SPEED_UP という定数を True にすると、画面の描画を止めるので、少し早くなります。
中程度のノートブックで、2500行くらいで 0.5秒くらいです。
それでも、30万行もあると、パソコンの性能にもよりますが、数分かかると思います。

バックアップを取ってから、試してみてください。

他3件のコメントを見る
id:a-kuma3

「ThisWorkBook」というので動かすと何も起きず。
「標準モジュール」で動かすと「型が一致しません」と出てきます。

回答では書き足りなかったのですが、マクロは「標準モジュール」で動作します。
んで、「型が一致しません」についてなのですが、もしかしたらキーワードを探す G列が数式になってないでしょうか?

試した感じでは、G列にエラーになっているセルがあると「型が一致しません」となります。
#REF! とか #DIV/0! とか。

もし、対象のシートがそのようになっているのであれば、エラーになっているセルがあったときに、

  • 無視して次の行を処理する(つまり、削除しない)
  • 処理を中断する(目で見て、人が判断して、G列の値を手で直す)

のどちらにするか決められるのであれば、マクロで処理ができると思います。

2015/02/28 16:31:50
id:moon-fondu

エラーのある行を削除して実行しました。
でも動かなかったんですが・・・G列の1行目と2行目が空白セルだったので、ここにデータを入れてから実行したら、うまくマクロが稼働しました!(88215行目で止まりましたが……データは入ってました)
ありがとうございましたm(_ _)m

2015/03/01 20:46:24
id:frkw2004 No.2

ふるるP回答回数192ベストアンサー獲得回数212015/02/25 13:36:10

ポイント40pt

こんな感じでしょうか。

Public Sub DeleteRow()
Dim KeyWord As String
Dim R As Long
Dim MaxRow As Long

KeyWord = "er" '削除の条件

'G列がキーなのでG列で最終行を取得する
MaxRow = Range("G" & Cells.Rows.Count).End(xlUp).Row

'最後の行から1行目まで後ろからループさせることで読み飛ばしを回避する
For R = MaxRow To 1 Step -1
If Range("G" & R) = KeyWord Then
Rows(R).Delete
End If
DoEvents
Next

End Sub


1行目から順に・・・とすると削除すると行がずれてしまうので、後ろから順にまわすのがセオリーです。

他3件のコメントを見る
id:moon-fondu

部分一致です!「#NAME?」と「#N/A」を消してやってみたら、やはり「実行中」とは出てくるんですが、マクロが動作しているときのWindows7の青丸がぐるぐるしている状態ではなく、動作していない白い十字の状態にすぐなってしまいます。
でもご回答いただきまして、ありがとうございます!

2015/03/01 20:46:43
id:frkw2004

部分一致の場合、If文の行を次のようにしてください。
If Instr(Range("G" & R),KeyWord)>0 Then
また、DoEventの前の行か後の行に次の行を追加してください。
Application.StatusBar = R & " / " & MaxRow
実行中はステータスバーに進行状況が表示されます。

2015/03/01 21:01:21
id:frkw2004 No.3

ふるるP回答回数192ベストアンサー獲得回数212015/02/25 13:47:28

ポイント10pt

そうそう、速くするには Application.ScreenUpdating = Falseにしておきましょう。 DoEventsで「応答なし」を回避しています。毎回のループではおおすぎるのならDoEventsの行を次のようにしてもいいでしょう。
If R Mod 100 = 0 Then
DoEvents
Application.StatusBar = R & " / " & MaxRow
End If
100行ごとにDoEventsとステータスバーへ進行状況のメッセージを書き込みます。
条件も「含む」であるなら Like とかInstrとかを使って書き直す必要があります。

id:Mook No.4

Mook回答回数1312ベストアンサー獲得回数3912015/02/25 23:30:54ここでベストアンサー

ポイント300pt

まともに行削除をすると数分オーダーなので、削除を一度にする例です。
セルの結合等あるとできないので、そのときは0ポイント配分で結構です。

作業列としてZ列を使用しているので、データがZ列以降にもある場合は、
コード中のZを十分大きな列名に変更してください。

30万行のデータで約3秒でした。

Option Explicit

Sub Main()
    Dim st
    st = Timer()
    DeleteRow "りんご", "G"
    Debug.Print Timer() - st
End Sub


Sub DeleteRow(keyWord As String, searchCol As String)
    Const DelNum = 9999999
    Const WorkCol = "Z"
    Dim lastRow As Long
    lastRow = Cells(Rows.Count, searchCol).End(xlUp).Row
    
    Dim kt
    kt = Cells(1, searchCol).Resize(lastRow)
    
    Dim st
    st = Cells(1, WorkCol).Resize(lastRow)
    
    Dim r As Long
    For r = 1 To lastRow
        If InStr(kt(r, 1), keyWord) = 0 Then
            st(r, 1) = r
        Else
            st(r, 1) = DelNum
        End If
    Next

    Cells(1, WorkCol).Resize(lastRow) = st
    
    ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add _
        Key:=Cells(1, WorkCol).Resize(lastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveSheet.Sort
        .SetRange Range("A1").Resize(lastRow, Cells(1, WorkCol).Column)
        .Header = xlGuess
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    Dim f As Range
    Set f = Columns(WorkCol).Find(DelNum, lookat:=xlWhole)
    If Not f Is Nothing Then Rows(f.Row & ":" & lastRow).Delete
    
    Columns(WorkCol).ClearContents
End Sub
他3件のコメントを見る
id:Mook

黄色くなったときに、r と keyWord、それから LasrRow の値は何でしょうか。
また G列の r 行にはどのようなデータがあったでしょうか。
変数の中身は、マウスカーソルを変数の上に持っていけば確認できます。

2015/02/28 15:16:54
id:moon-fondu

あ、知らなかったです、確かにマウスカーソルを持っていったらr=77643行目がヒットしておりまして、そこに「#NAME?」と記載されてました!
消してもう1回やると、次は132329行目に「#N/A」とありました。
それも消して、もう1回やると・・・うまくいきました!
ありがとうございますヽ(´▽`)/

2015/03/01 20:47:03
  • id:ceramic-cups
    フィルタで「削除したい条件以外」に絞っておいて、[Ctrl]+[Shift]+[:]でテーブル範囲指定してコピーして、新規シートに貼り付け。と、すると削除した結果のテーブルの貼り付けができそうですが、件数が多いので結果の保証はできません。
    既にマクロの回答がでているのでマクロで動作させた方が確実でしょうね。

    世良満久
  • id:moon-fondu
    ああ、そういう方法もあるんですね、フィルタで「削除したい条件以外」を指定できるのは知りませんでした。
    ありがとうございます!
  • id:ceramic-cups
    補足です。
    目的のテーブルにフィルターを設定して、削除したい文字列のある[▼]ボタンを押して、[テキストフィルター]→[指定の値を含まない]に文字列を入力します。
    テーブルが絞られた後、テーブルの一部をクリックしてから、[ctrl]+[shift]+[:]を押すと絞られた結果のみが選択されますのでそれをコピーして新規シートに貼り付けてください。
    件数が多いので時間がかかったりエラーが出るかもしれません、ご注意ください。

    データ件数的にはAccessで扱った方が良いのではないかとも思います。
    Accessでデータを蓄積して、クエリーで絞り込んで、その結果をExcelからAccessの外部データの取り込みする、といった方法とかですね。
    予算的にAccessを購入するのが難しいようであれば、LibreOfficeBaseをインストールしてデータの絞り込みを行ってからExcelで読める形式で出力するとか。

    世良満久


  • id:moon-fondu
    ああ、そのやり方もいいですね、ありがとうございます!
    「指定の値を含まない」は使ったことないので非常に参考になりました。
    ありがとうございます!
    Accessは全く触ったこともありませんでして……LibreOfficeBaseって難しそうですね!
    ちょっと導入は控えておきます(^_^;)

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません