[[20141119154419]]　『別シートの情報を参照し、重複したら削除をしたい』（もも）　ページの最後に飛ぶ

『別シートの情報を参照し、重複したら削除をしたい』（もも）

Excel2010のVBA

ttp://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q1057112375
この質問と同じ事をしたいです。

シート１のA列には個別番号があり、
シート２のA列にはシート１の削除したい個別番号があります。

シート２のA列に載っているデータがシート１にあった場合、
シート１の該当する行を削除したいのです。

そこで、下記のようにコードを書きましたが、うまく作動しません。
ステップインしてみると、ここで躓いているようなのですが↓
If WorksheetFunction.CountIf(ws2.Columns("A"), ws1.Range("A" & r)) > 0 Then
どこをどう修正したら良いものか知恵をお授けください。

Sub sample()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim lastRow1 As Long
Dim r As Long
Set ws1 = Sheets("シート１")
Set ws2 = Sheets("シート２")
lastRow1 = ws1.Range("A" & Rows.Count).End(xlUp).Row
For r = lastRow1 To 2 Step -1
If WorksheetFunction.CountIf(ws2.Columns("A"), ws1.Range("A" & r)) > 0 Then
ws1.Rows(r).Delete
End If
Next
End Sub

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

lastRow1は，どうなってますか？
（Hara） 2014/11/19(水) 16:09

Haraさま
最終行がどうなっているかということでしょうか？
4800行くらいありますが、その時によって変動します。
ですので、データのある最下行まで取得したいです。
（もも） 2014/11/19(水) 16:23

lastrow1には４８００とかになってますか。
確認しましょう。動くと思いますが。
（Hara） 2014/11/19(水) 17:01

lastRow1は質問文の最後に記載の通り、
lastRow1 = ws1.Range("A" & Rows.Count).End(xlUp).Row 　となっています。
lastRow1 = ws1.Range("A5000")　にしてみましたが、それでも重複しているデータが消えません。

（もも） 2014/11/19(水) 17:13

こんにちは

＞それでも重複しているデータが消えません。
どのように「重複している」と判断されていますか？
本当に CountIf で一致しているとみなされているか、
なぜ一致していないのか(たとえば余分な空白がないか、全半角は統一されているか)、
などの確認も必要かと思われますが、いかがでしょうか

　
（みねっと） 2014/11/19(水) 17:21

みねっと様

遅くなって申し訳ありません。
コメント拝見した後、シート１と２、それぞれのデータを見比べてみました。
空白や半全角、セルの書式設定(標準、文字列etc)も相違ありません。
（もも） 2014/11/20(木) 09:01

 新しいブックにシート１とシート２のデータをコピペしてから処理を実行してみてはいかがでしょうか
 コピペする際は値の貼りつけを選択してください
（fbug） 2014/11/20(木) 09:23

＞　それぞれのデータを見比べてみました。
文面からは「目視」チェックしかしていないように感じるのですが。。。

もっと単純なテスト様式を作成して、きちんと動作するかの確認も有効です。
以下のようなテストデータを作成して、提示されているコードを実行したところ

＜シート：シート１＞
　　 A

 1   No
 2  B123
 3  D１２３
 4  F123
 5  E123_      ※ _ は半角スペース
 6  A123
 7

＜シート：シート２＞
　　 A

＜結果：シート１＞
　　 A

 1   No
 2  D１２３
 3　F123
 4  E123_
 5

で、一致している行はきちんと削除されております。
よって、やはりCOUNTIF関数が「１」を返していないのではないかと思われます。
（みねっと） 2014/11/20(木) 09:33

こんにちは

別案なんですけど、

Sheet1、Sheet2 のA1には同じ項目名が入っているとして

Sub test()

    On Error GoTo Err_Line
    With Worksheets("Sheet1").Range("A1").CurrentRegion
        .Columns("A:A").AdvancedFilter _
            Action:=xlFilterInPlace, _
            CriteriaRange:=Worksheets("Sheet2").Range("A1").CurrentRegion, _
            Unique:=False
        .Offset(1).Columns("A:A").SpecialCells(xlCellTypeVisible).EntireRow.Delete
    End With
    Worksheets("Sheet1").ShowAllData
    Exit Sub
Err_Line:
    MsgBox Err.Description
End Sub

とするとどうなりますか？

（ウッシ） 2014/11/20(木) 09:35

fbugさま
コメントありがとうございます。
コメント頂いている最中にああああ　いいいい　と全く関係ないデータで試し、作動しました。
(みねっとさまもありがとうございます)

仰るとおり新しいブックに貼り付けをしようとしたら
図のコピー扱いになり、そもそも貼れないという問題が発生しました。
元データに原因がありそうです。
引き続き、ウッシさまの案も含め、色々試して見ます。
（もも） 2014/11/20(木) 09:39

ウッシさまのコードを実行してみたところ、
「インデックスが有効範囲にありません」と出ましたので
Ａ列が存在していないことになりますね。

目視だと確かにA列にありますし、
vlookupやCtl+F等、手作業で検索かけるときちんと合致するのですが･･･。
（もも） 2014/11/20(木) 09:47

＞「インデックスが有効範囲にありません」と出ましたので
そのまま貼り付けたのであれば、たぶん、シート名の問題かと。

（みねっと） 2014/11/20(木) 09:58

こんにちは

済みません、シート名は適宜変更して下さい。

データは値として貼り付けるか「区切り位置」を何も指定せずに実行完了してみるといいかも。

（ウッシ） 2014/11/20(木) 10:03

＞図のコピー扱いになり、そもそも貼れないという問題が発生しました。
こちらはエクセル自体を、別のプロセスで起動していたり？
小出しの回答ですみません。

（みねっと） 2014/11/20(木) 10:11

言葉足らずですみません。
シート名は自分のファイルに合わせて変更しております。

＞こちらはエクセル自体を、別のプロセスで起動していたり？
おっしゃる通りでした。
同じプロセスで起動し、貼り付けできました。
が、やはりインデックスが～になります。
(前述の通り、シート名の部分は変更してあります)

元データを見直したほうがよさそうですね
（もも） 2014/11/20(木) 10:14

こんにちは

インデックスが～

とすると、やはりシート名だと思います。

Sheet1、Sheet2 の部分を変更して試して下さい。

Sub test()

    Dim sh1 As Worksheet
    Dim sh2 As Worksheet

    Set sh1 = Worksheets("Sheet1")
    Set sh2 = Worksheets("Sheet2")

    On Error GoTo Err_Line

    With sh1.Range("A1").CurrentRegion
        .Columns("A:A").AdvancedFilter _
            Action:=xlFilterInPlace, _
            CriteriaRange:=sh2.Range("A1").CurrentRegion, _
            Unique:=False
        .Offset(1).Columns("A:A").SpecialCells(xlCellTypeVisible).EntireRow.Delete
    End With
    sh1.ShowAllData
    Exit Sub
Err_Line:
    MsgBox Err.Description
End Sub

（ウッシ） 2014/11/20(木) 11:27

ウッシさん記載のコードですとシート名を置き換えても
やはり同様のエラーになります。
(Ctl+Hでも念の為置き換え場所を確認しました)

文字列なのか標準なのか、何度か試すと
一番最初のコードでできたりできなかったりすることが判明しました。

最後にvlookup等、手作業で重複がないか確認するしかないのかと諦めかけております。
（もも） 2014/11/20(木) 16:02

A列が文字列になって個別番号の末尾にスペースが入っているとかはないですか？

他のDBから取込んだデータとかの場合問題が発生することがあります。

「区切り位置」は試してみましたか？

（ウッシ） 2014/11/20(木) 16:14

スペースは入っていないです。
区切り位置も試した上で、vlookupを試してみましたが、それだときちんと反応します。
試すたびにできるとき、できないときがあるので不可解です。
（もも） 2014/11/20(木) 16:47

結合セルとかは無いですよね？

出来ないときのSheet1、Sheet2のA列データの一部でもいいので掲載してみて下さい。

（ウッシ） 2014/11/20(木) 16:53

ウッシさま

大変遅くなりました。
色々試してみたのですが、ステップインだとエラーになり、
マクロの実行だときちんとできているようです。

ちなみにステップインの場合、
　If WorksheetFunction.CountIf(ws2.Columns("A"), ws1.Range("A" & r)) > 0 Then
★ws1.Rows(r).Delete
　End If

★の行が飛ばされて
　If WorksheetFunction～とEnd Ifを延々と繰り返している状態です。
（もも） 2014/11/22(土) 20:03

こんばんは

こちらで提示したコードじゃないですね。

別案で、と断った通り
提示したコードだけでテストして下さい。

（ウッシ） 2014/11/23(日) 18:29

コメント返信：

[ 一覧(最新更新順) ]

[[20141119154419]] 『別シートの情報を参照し、重複したら削除をしたい』（もも） ページの最後に飛ぶ

[[20141119154419]]　『別シートの情報を参照し、重複したら削除をしたい』（もも）　ページの最後に飛ぶ